Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Problem with connection argument in CMDEXEC command


Problem with connection argument in CMDEXEC command

Author
Message
rburko
rburko
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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?
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2009 Visits: 1189
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)
rburko
rburko
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8326 Visits: 19472
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
rburko
rburko
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search