Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with connection argument in CMDEXEC command Expand / Collapse
Author
Message
Posted Wednesday, May 1, 2013 12:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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?
Post #1448566
Posted Wednesday, May 1, 2013 12:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 11:09 PM
Points: 1,940, Visits: 1,173
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)
Post #1448572
Posted Wednesday, May 1, 2013 12:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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.
Post #1448576
Posted Thursday, May 2, 2013 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:02 PM
Points: 5,259, Visits: 12,193
Can you create a connection name which does not contain any spaces and try that?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1448714
Posted Thursday, May 2, 2013 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 6:40 AM
Points: 41, Visits: 365
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.



Post #1448859
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse