Executing a SSIS Package from Stored Procedure in SQL Server

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.

Problem Statement

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?

Executing a SSIS Package from Stored Procedure

There are basically two ways you can execute your SSIS package from the user stored procedure. First, you can create a SQL Server Agent job that would have a step to execute the SSIS package, and wherever you want to execute it, you can use sp_start_job system stored procedure to kick off the job, which in turn will execute the package. You might not have any schedule attached to the job if you just want to execute it from the user stored procedure, when you don’t want your job to be kicked off (SSIS package to be executed) on a defined schedule time/interval. The disadvantage of using this approach is you don’t have an easy way if you have to pass some values (assign values to SSIS package variable) at run time. You might need to have a metadata table from where your SSIS package will consume/retrieve the runtime values.

Second, you can enable xp_cmdshell extended stored procedure, and using it you can execute DTEXEC utility to execute your SSIS package. The disadvantage of using this approach is that enablement of xp_cmdshell poses security threats (operating system level access) and hence by default it’s disabled. However using this approach provides finer level control of passing SSIS package variables’ runtime values easily. In this article, I am assuming the first approach is simple and straightforward and thus I will jump directly to the second approach.

Please make sure you have enabled xp_cmdshell component or extended stored procedure, or else you will get an exception like this:

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

Enabling xp_cmdshell

To enable xp_cmdshell you need to use sp_configure system stored procedure with advance options like this:

Enabling xp_cmdshell component


USE master
GO
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
-- WITH OVERRIDE disables the configuration value checking if the value is valid
RECONFIGURE WITH OVERRIDE
GO
-- To enable the xp_cmdshell component.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-- Revert back the advance option
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
GO

Executing a SSIS package stored in SQL Server from the user stored procedure

Once xp_cmdshell extended stored procedure is enabled, you can execute any operating system command as a command string. In our case, we will be using DTEXEC utility of SSIS to execute the package. Since the SSIS package is stored in SQL Server, we need to use /SQL switch with DTEXEC command and /SET switch to set the values of SSIS variables as shown below:

DTEXEC calls SSIS package from SQL Server


DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'

SET @SQLQuery = 'DTExec /SQL ^"\DataTransfer^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

Figure 1 - DTEXEC calls SSIS package from SQL Server

Figure 1 – DTEXEC calls SSIS package from SQL Server

 

Executing a SSIS package stored in file system from the user stored procedure

As our SSIS package is now stored in the file system, we need to use /FILE or /F switch with DTEXEC command and /SET switch to set the values of SSIS variables as shown below. You can see that executing the xp_cmdshell extended stored procedure it outputs, if there is any, results in rows of texts. If you don’t want this output to be returned, you can use second parameter (NO_OUTPUT) of this stored procedure:

DTEXEC calls SSIS package from File System


DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY'

SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

Figure 2 - DTEXEC calls SSIS package from File System

Figure 2 – DTEXEC calls SSIS package from File System

Notes

 

  • xp_cmdshell is an extended stored procedure that lets you execute operating system commands and returns any output as rows of texts. This extended stored procedure runs in synchronous mode; that means control will not return until the command completes. By default only sysadmin can execute this extended stored procedure but permission can be granted to other users with a proxy account; for information, click here.
  • DTEXEC is a command line utility for SSIS configuration and SSIS package execution from SQL Server, SSIS service and file system sources. After execution, this utility returns different exit codes that indicate what happened during execution; for more information click here.

Conclusion

In this article I talked about executing a SSIS package from user defined stored procedure. In the first approach, we created a job, making SSIS package call as a job step and executing it by calling sp_start_job system stored procedure from the user defined function. In the second approach, we enabled xp_cmdshell to execute DTEXEC command line utility from the user defined stored procedure.

 

Resources

MSDN xp_cmdshell

MSDN dtexec Utility

 

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles