Execute SQL Server SSIS Package From Stored Procedure
Solution 1
I don't think so, here are two good articles:
http://www.simple-talk.com/sql/sql-server-2005/executing-ssis-packages-/
http://www.codeproject.com/KB/database/Call_SSIS_from_SP.aspx
Solution 2
I recommend using Agent instead:
- Create Agent proxy account for the account that will run the job
- Create Agent job that runs this package
- Make it use the proxy account created in #1
- Test the job
- In SQL code, use
sp_start_job
to start this job
The downside is that you can't easily pass the parameters from SQL to the package this way.
Solution 3
Since 2012, MSSQL has an SSIS Catalog, where you can park your packages there. You can then execute a package with parameters. This way we can avoid xp_cmdshell
to call dtexec
, or sp_start_job
without parameters. Key SPs: create_execution
, set_execution_parameter_value
, and start_execution
.
From MSDN:
To run a package on the server using SQL Server Management Studio
Deploy and Execute SSIS Packages using Stored Procedures
Admin
Updated on August 19, 2020Comments
-
Admin over 3 years
I have a SSIS package that is stored in a SQL Server 2005 DB. I am trying to execute this package from a stored procedure on the same server. Is there a better way than exec master..xp_cmdshell 'dtexec /SQL...
I am running into (I think) file system permission issues with xp_cmdshell that is preventing execution