CODEWITHSUNDEEP
sql-serverstored-proceduresssis
Robert
Robert

Reputation:

Execute SQL Server SSIS Package From Stored Procedure

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

Upvotes: 8

Views: 23552

Answers (3)

Irawan Soetomo
Irawan Soetomo

Reputation: 1325

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

Upvotes: 1

Michael Entin
Michael Entin

Reputation: 7764

I recommend using Agent instead:

  1. Create Agent proxy account for the account that will run the job
  2. Create Agent job that runs this package
  3. Make it use the proxy account created in #1
  4. Test the job
  5. 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.

Upvotes: 4

KM.
KM.

Reputation: 103697

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

Upvotes: 4

Related Questions