none
Running SSIS from Command Line w/ SQL Agent

    Question

  • 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

     

     

    Tuesday, April 17, 2007 2:11 PM

Answers

  • 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

     

    Tuesday, April 17, 2007 6:14 PM
    Moderator

All replies

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

     

    -Jamie

     

    Tuesday, April 17, 2007 2:16 PM
    Moderator
  • 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

    Tuesday, April 17, 2007 5:39 PM
  • 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

     

    Tuesday, April 17, 2007 5:54 PM
    Moderator
  • 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

    Tuesday, April 17, 2007 6:09 PM
  • 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

     

    Tuesday, April 17, 2007 6:14 PM
    Moderator
  •  

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

     

    Anthony Akin

    Tuesday, April 17, 2007 7:03 PM
  •  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

     

    Tuesday, April 17, 2007 7:22 PM
    Moderator
  • Jamie,

     

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

     

    Anthony Akin

    Tuesday, April 17, 2007 8:26 PM
  •  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...

    Tuesday, April 17, 2007 10:46 PM
    Moderator