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.
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.
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.
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. )
