Running SSIS from Command Line w/ SQL Agent

Hi Everyone!

I'm trying to run have SQL Server Agent excute an SSIS package from the command line and I keep recieving an error message. I will assume that I have miss typed something. Can someone validate that the execute line looks workable?

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /F "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" /CONNECTION "APLUS70F70.AKIN";"\"uid=AKIN;Dsn=APLUS70F70;\"" /CONNECTION "CRMPSQL.IBT_Aplus";"\"Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Thanks in advance!

Anthony Akin

Anthony, the " directly after dtexec.exe looks rogue to me.

-Jamie

|||

Jamie,

Thanks for the quick reply! A little background might help! I'm running Windows Server 2003 x64 and SQL 2005. We oringinally were recieving errors because of the BIDS trying to run the 64 bit ODBC's and all the information on the web said to run it from the command line. I can run the package listed below from the command line, but I cannot use SQL Server Agent to running this command. Any thoughts or suggestions would be greatly appreciated!

"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"/FILE "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" /CONNECTION "APLUS70F70.AKIN";"\"uid=AKIN;Dsn=APLUS70F70;\"" /CONNECTION "CRMPSQL.IBT_Aplus";"\"Data Source=CRMPSQL;Initial Catalog=IBT_Aplus;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW

Thanks,

Anthony Akin

|||

Sorry yeah you're right. The command-line syntax looks good.

What's the error message? usually if something works from dtexec.exe but not SQL Agent it will be a permissions issue.

-Jamie

|||

Jamie,

Here is what is in the log file. Is it because I'm calling out the 32bit dtexec with a 64bit program?

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
04/17/2007 13:00:08,Customer_Master,Error,0,CRMPSQL,Customer_Master,(Job outcome),,The job failed. The Job was invoked by User IBTINC\Administrator. The last step to run was step 1 (CustomerMaster).,00:00:00,0,0,Anthony Akin,,,0
04/17/2007 13:00:08,Customer_Master,Error,1,CRMPSQL,Customer_Master,CustomerMaster,,Executed as user: CRMPSQL\SYSTEM. The step failed.,00:00:00,0,0,,,,0
04/17/2007 13:00:08,Customer_Master,Unknown,1,CRMPSQL,Customer_Master,CustomerMaster,,Microsoft (R) SQL Server Execute Package Utility<nl/>Version 9.00.3042.00 for 32-bit<nl/>Copyright (C) Microsoft Corp 1984-2005. All rights reserved.<nl/><nl/>Option "F:\Projects\SSIS\CustomerMaster_1\CustomerMaster_1\Package.dtsx" is not valid.,00:00:00,0,0,,,,0

Thanks

Anthony Akin

|||

No idea. You kinda need to get hold of the output from dtexec.exe

You should also try executing using dtexec.exe from the command-line as the same user urnning SQL Server Agent.

-Jamie

|||

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

|||

A.Akin wrote:

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

I think you can, yeah. I'm not sure, I'm no expert on SQL Agent.

-Jamie

|||

Jamie,

I decided on build .bat files instead of messing around with SQL server agent. Thanks for the input today!

Anthony Akin

|||

A.Akin wrote:

I plead ingnorance - How do change the user? Can I set up a proxy?

Anthony Akin

It may be too late to help you on this, but for future reference - you can run the command prompt as a different user by right-clicking on the short-cut (usually located under Accessories) and choosing Run As...