SSIS: Execute Package using the Stored Procedure in T-SQL

 

Introduction

This is another article in TechNet Wiki series for this May. This article will allow you to know more about how you can execute the SSIS packages from SSMS T-SQL script itself.

Overview

While working, we built an SSIS package by using BIDS. We can easily execute it from BIDS project but we want to do it from the SQL query from SSMS because we don't want to open the BIDS and execute the package manually while working in SSMS. So, after some research, we found it can be done by using a stored procedure called xp_cmdshell.

In Brief

Generally, talking about this store procedure xp_cmdshell: "xp_cmdshell" is an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code. If needed, the output of these commands will be returned to the calling routine.

To enable this we can simply run the script as:

USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH  OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH  OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO

Alternatively, we can do it by using the "SQL Server 2005 Surface Area configuration" in SQL Server 2005 onwards.

Then, you can run dtexec from the xp_cmdshell prompt.

The following example shows how to run a package called Package.dtsx and ignore the return code:

EXEC xp_cmdshell 'dtexec /f "C:\Package.dtsx"'

Where the dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages.

Also, if we need a return code from SSIS package to SSMS then the following example shows how to run the same package and capture the return code:

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\Package.dtsx"'

Summary

From this article, you are able to execute the SSIS package by using SSMS T-SQL procedure.