I'm putting this under the SQL Server 2005 Integration Services subject because I'm trying to execute a package using a SQL Server Agent Job and I can't get the change I need to make to work.
To make a long story short, I have to use the "Operating System(CmdExec)" type when using the SQLl Server Agent Job because my package uses Excel and I have to access the 32-bit DTExec.exe. I'm having trouble with my package database connections, in all likelihood related to the lack of a password in the connection string. So, I went to the Execute Package Utility (DTExecUI), added a password to the connection string under the connection managers, went to the command line, saw that the connection command was there ("How nice," I thought, "DTExecUI did all the work for me"), tested everything (everything ran just fine), copied the command line text, then pasted the part I needed into the command of my SQL Server Agent Job. But it didn't work.
Here is an example of my command:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\MyFile\MyPackage.dtsx" /DECRYPT "mypassword"/CONNECTION "My Connection String Name";"\"Data Source=MyServer;User ID=myuserid;pwd=thepassword;Initial Catalog=MyCatalog;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False;\"" /CONNECTION "MySecond.Connection.String";"\"Data Source=MyServer;User ID=myuserid;pwd=thepassword;Initial Catalog=mycatalog;Persist Security Info=True;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
And here is an example of the error message I'm getting from the agent job:
Argument "My" for option "connection" is not valid. Process Exit Code 6. The step failed.
So what's wrong? And why does it work when using DTExecUI? I seem to remember that in the past I've had to edit some apostrophes when using the DTExecUI command text in an agent job but I can't remember what I did. Or, is it that there are spaces in "My Connection String Name?" When I've tried to search for the correct syntax, there is paltry information available online.
If I go back to my original command text without the database connection information, everything works fine (well, except for the password problem when trying to make the connection), so I don't believe this is a problem with running a command in a job