Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Sunnyvale, CA USA

    Unanswered: Executing an SSIS package from TSQL without using xp_cmdshell?

    How can I execute an SSIS package from TSQL without using xp_cmdshell?

    I have a web-app which calls some SQL which executes my SSIS package (a DTSX file, but stored in the server). But the security policy for my application won't permit me use to xp_cmdshell.

    I want to do this:-
    DECLARE @returncode int
    EXEC @returncode = xp_cmdshell 'dtexec /sq pkgOne"'

    Is there another way for executing a Package without going to the command line (e.g. is there some other system stored proc)?


  2. #2
    Join Date
    May 2004
    Columbus, OH
    andyabel, could create a SQL job that has the command to execute xp_cmdshell in it and then in your web app run the following in TSQL

    EXEC MSDB..SP_Start_Job @Job_Name = 'Your Job Name Here'

    The SQL job would not be scheduled to run and would only be run when you tell it to via your app. You would not have to worry about the security policy because the job is going to fire on the SQL server and that is where the xp_cmdshell is going to run from.

    The bad is that the SSIS return code is going to be passed back to the SQL job and not your web app. But, if you post the return code to a table on your database and then have your app scan that table you can get the code that way...if you need it.
    Life....Just another opportunity to live another day like a pirate....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts