Recently we had a requirement to execute a SSIS package from one of our user stored procedures, but there is no direct way available in SQL Server for executing a SSIS package from a stored procedure. SQL Server has some undocumented system stored procedures for SSIS package management, but none is available that can be used to execute a SSIS package from the stored procedure. Now the question is, how can we execute a SSIS package from the user stored procedure? If the direct method is not available, is there any alternative for this?
Here are two different ways a SSIS package can be executed from a stored procedure. In the first approach, we will create a job, making SSIS package call a job step and executing it by calling the sp_start_job system stored procedure from the user defined function. In the second approach, we will enable xp_cmdshell to execute the DTEXEC command line utility from the user defined stored procedure, for information click here.
No comments:
Post a Comment