May 1, 2013 at 12:41 pm
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
Any ideas?
May 1, 2013 at 12:48 pm
hey am not expert in ssis.but i think you need to put equal for CONNECTION ="MySecond.Connection.String";
Malleswarareddy
I.T.Analyst
MCITP(70-451)
May 1, 2013 at 12:56 pm
Well, it was worth a shot Malleswarareddy but, unfortunately, that didn't work. I just get this error:
Option "/CONNECTION=MY" is not valid. Process Exit Code 6. The step failed.
But thanks for the suggestion.
May 2, 2013 at 1:31 am
Can you create a connection name which does not contain any spaces and try that?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 2, 2013 at 9:20 am
Phil, I thought I had already tried that but apparently I didn't (or didn't do it correctly). I changed the connection string name from "My Connection String Name" to "My_Connection_String_Name". I suppose that worked because it seems like I got further than before but I'm still getting an error. Now I'm getting
Option "/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;" is not valid. Process Exit Code 6. The step failed.
And again, here is the original string example (with the connection string name change):
"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
I thought maybe the error was due to those double quotes at the end of the connection (\"" /) so I removed one of them but that didn't work. Or maybe it needs to be ";\"? I'm not sure. Again, this was generated from DTExecUI so my assumption would be that it should be correct (again, it worked when executing the package from DTExecUI).
Wow. They really don't give you much to go on when trying to debug this stuff.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply