question

w_w avatar image
w_w asked

Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed.

I have a SSIS package run fine in BID, but when run as a sql agent job, it failed at the error: Argument "Server1" for option "connection" is not valid. The command line parameters are invalid. The step failed. In Job Step Properties, Configuration, Command files, Execution options, Logging, Set Values, Verification tabs do not have anything set. The only change made is in Data Source tab, the server name is changed to server2 ,so is in Command Line tab changed accordingly. But, somehow, it still points to the connection inside of the package. If I change the connection in the package, then create a new job point to the changed package, then it runs fine. Why changing DataSouce in the job properties will not take effect? Here is what is Command Line tab: /FILE "C:\ Conv_01.dtsx" /CONNECTION "Connection001";"\"Data Source=server2\SQL2008;Initial Catalog=FO;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{849C75AD-4693-4DF2-9EC7-73C347FEE10C}HEC_1.US-Iv2.5;\"" /CONNECTION " Connection002";"\"Data Source=server2\SQL2008;Initial Catalog=ULL;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-Package-{D399B84F-1467-41C3-A292-5B27789B66A3}HEC_EL1.ULL;\"" /CHECKPOINTING OFF /REPORTING E in Set Values tab,I set I set Properties Path to: \Package001.Variables[User::ConnectionString001].Properties[Value] and Vaule is the actual connection string. but still got the same error.
ssissql-agent
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you get the same error when attempting to run it from the command line using DTEXEC? Normally, when changing database connections within jobs I like to have the connection strings as variables and use the "Set Values" tab to alter them to the relevant connections at run time (alternatively you could use the Data Sources tab to set them at run time). This would update the Command Line value for you so there would be no manual changes to that required.
0 Likes 0 ·
It does not have vaule set in Command file tab. The package runs as a sql agent job at the custome site, DTEXEC will not used.
0 Likes 0 ·
The question is a little unclear, are you actually using a variable to set the connection string in the package at run time?
0 Likes 0 ·
No, i don't use variables to set connection strings. I have connection strings in Connection Manager. That's why when the package runs as a sql agent job, I tried to re-set the connection string in DataSource tab in the job step properties tab, but it got the error which it still looks for the connection string inside the package.
0 Likes 0 ·
When I check the box and change the Connection String in the Data Sources tab I see the change reflected on the Command Line tab when I take a look. You mention that you change a value for a variable in the Set Values tab. What is that variable used for?
0 Likes 0 ·
Show more comments

1 Answer

·
Rob Farley avatar image
Rob Farley answered
Create a credential in SQL for your account. Then make a proxy in SQL Agent for that credential, and let it run SSIS tasks. Now go to your job step and tell it to use that proxy. If the job works, create a new user and start giving it permissions until the job runs successfully. Things like permissions to read the .dtsconfig files might help. )
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.