10-22-07, 12:31 #1Registered User
- 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)?
10-23-07, 09:35 #2Registered User
- Join Date
- May 2004
- Columbus, OH
Well....you 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....