Run SSIS using xp_cmdshell in a SQL Server stored procedure

By: Tim Ford | Comments (5) | Related: 1 | 2 | 3 | 4 | More > Integration Services Execute Package Options

Problem

There are several ways that you can run an SSIS package and sometimes there may be a need to run a package directly from a query window or from within a stored procedure which can be done using xp_cmdshell.

Enabling xp_cmdshell does come with a price; that is why it is disabled for a reason. Anyone who is able to access your SQL Server instance may then be able to "shell-out" as an Administrator with full administrative control to the server that is hosting the SQL instance. This is a very easy way for even the novice hacker to gain access to your domain.

This tip is not about the pros and cons of xp_cmdshell, I just issued this bit of background to give you fair warning of what lies ahead when you toy with xp_cmdshell. What this tip is about however is how to use xp_cmdshell to run SSIS packages from within a stored procedure: how to pass parameters to the SSIS package, and in-turn, what security steps you need to take to ensure you (a) allow use of xp_cmdshell with as low-level access as possible to the SQL Server instance and (b) what considerations need to be made for security inside the database that the SSIS package is using for its data source.

Solution

One can call an SSIS package from T/SQL via the xp_cmdshell stored procedure by simply passing it a command line statement that can be dynamically configured via Transact/SQL.

Xp_cmdshell is the stored procedure that allows for SQL Server to interact with the Windows operating system. I've even seen it referred to as SQL Server's DOS prompt, which is a simplified, yet accurate description too! By default, xp_cmdshell is not enabled upon initial install of Microsoft SQL Server, however, but using either T/SQL or the facets GUI in SQL Server Management Studio (or the Surface Area Configuration Tool in SQL Server 2005) you can easily enable xp_cmdshell. The following code for enabling xp_cmdshell is straight from Microsoft TechNet:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Once xp_cmdshell has been enabled you can issue T-SQL such as this:

DECLARE @Path VARCHAR(200),
 @SQLServer VARCHAR(50),
 @DB VARCHAR(100), 
 @EmailAddress VARCHAR(500),
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000)

SELECT @Path = 'E:\SSIS\Packages\'
SELECT @SQLServer = 'MSSQLTIPS01' 
SELECT @DB = 'Foo_Dev'
SELECT @EmailAddress = '[email protected]'

SELECT @Cmd = 'DTexec /FILE "' + @Path + 'Foo.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW'
 + ' /SET \Package.Variables[User::varSourceSQLServer].Properties[Value];' + @SQLServer 
 + ' /SET \Package.Variables[User::varErrorNotifyEmail].Properties[Value];' + @EmailAddress


EXEC @ReturnCode = xp_cmdshell @Cmd

IF @ReturnCode <> 0
BEGIN
 SELECT @Msg = 'SSIS package execution failed for ' + @path + 'Foo.dtsx on SQL Server\Instance: ' + @SQLServer + '.' + @DB
 EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress , @body = @Msg, @subject = 'SSIS Execution Failure'
END;

The net result is that the following command is passed out to the O/S, which then runs the SSIS package with the values:

DTexec /FILE "E:\SSIS\Packages\Foo.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW   /SET \Package.Variables[User::varSourceSQLServer].Properties[Value];MSSQLTIPS01   /SET \Package.Variables[User::varErrorNotifyEmail].Properties[Value];[email protected]

This is great for using the same package to run against different environments, say perhaps Development, QA/Test, and Production. One package, and by passing in the SQL instance and database names you can execute (from T/SQL) against different instances depending upon your needs. Furthermore, you can pass in different email addresses for notification matters depending upon your needs. You define variables for the SQL instance and database for your data source connection in SSIS along with an email address variable for notification purposes. You define expressions in SSIS to dynamically set the connection string of the data source based upon your variable values. Then, by dynamically building the DTEXEC command in T/SQL and passing the values of those variables from T/SQL into the DTEXEC command via xp_cmdshell you can drive the behavior of your SSIS package without touching Business Intelligence Development Studio (BIDS) at all.

Furthermore, you can wrap all of this code into a stored procedure making it much easier to call:

CREATE PROCEDURE [dbo].[usp_LaunchSSIS] 
 @Path VARCHAR(200), 
 @SQLServer VARCHAR(50), 
 @DB VARCHAR(100), 
 @EmailAddress VARCHAR(500)
AS

SET NOCOUNT ON

DECLARE 
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000)

SELECT @EmailAddress = QUOTENAME(@EmailAddress,'"')
SELECT @SQLServer = QUOTENAME(@@servername,'"')
SELECT @Cmd = 'DTexec /FILE "' + @Path + 'Foo.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW'
 + ' /SET \Package.Variables[User::varSourceSQLServer].Properties[Value];' + @SQLServer 
 + ' /SET \Package.Variables[User::varErrorNotifyEmail].Properties[Value];' + @EmailAddress

EXEC @ReturnCode = xp_cmdshell @Cmd

IF @ReturnCode <> 0
BEGIN
 SELECT @Msg = 'SSIS package execution failed for ' + @path + 'Foo.dtsx on SQL Server\Instance: ' + @SQLServer + '.' + @DB
 EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress , @body = @Msg, @subject = 'SSIS Execution Failure'
END

RETURN @ReturnCode
GO

You can then simply call the stored procedure as such to get the same results as the earlier code statement:

EXEC dbo.usp_LaunchSSIS 'E:\SSIS\Packages\', 'MSSQLTIPS01', 'Foo_Dev' '[email protected]';

Credit goes to both Brian Nichols and Jeff Block for exposing me to this construct. Part two of this tip series will explain how to secure xp_cmdshell access after you've enabled it on the SQL Server instance and what this means when it comes to rights to the database objects that are accessed within the SSIS package being called via xp_cmdshell.

Next Steps

Learn the various techniques for enabling xp_cmdshell with this tip.
Learn how you can run a DOS command from SQL Server without using xp_cmdshell at MSSQLTips.
Read about how you can execute command line scripts via SQL Agent jobs here.
Alter this code to work with a real SSIS package in your environment.

About the author

Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Friday, July 24, 2020 - 9:28:49 AM - nabin	Back To Top (86188)
While executing xp_cmdShell from another stored procedure, i am getting "Access denied" error. Is there any way to solve this issue?

Thursday, May 24, 2012 - 8:35:12 AM - Mr DBA

Why are you using xp_cmdshell??? It it not recommended for use and should be disable forever as it is a bad security policy.

Tuesday, March 1, 2011 - 5:19:57 PM - opc.three

I would categorize this article as an invitiation to implement a risky design pattern as it increases the attackable surface area of the SQL Server instance on which it is used. I reserve the method outlined in this article as a one-off way to call an SSIS package only in a carefully controlled SQL Server environment, not for use within an interactive application.

For those reading this article considering this method as the primary design pattern for an application I inherited a system employing this method in an enterprise ETL system and it worked fine. I would however recommend looking at child packages in SSIS as a preferred alternative. A parent SSIS package can retrieve application configuration data from a database table or a dtsconfig file and call a child SSIS package setting the package location and its internal properties dynamically thereby alleviating the need for xp_cmdshell. SSIS also allows you more logging and exception management facilities than T-SQL. Your parent package can be called from a SQL Server Agent job as needed just as a stored proc would be.

The only way to avoid xp_cmdshell is to design with such goals in mind. The DBA team will thank you as will the business users responsible for conducting your company's security audits.

Friday, October 22, 2010 - 9:52:21 AM - Tim

Shelling out to the command prompt only works if the server has not been upgraded from 2005 to 2008.

In that scenario, there are 2 binaries for DTSEXEC, one in the 90 directory (for SQL 2005) and one in the 10 directory (for SQL 2008). Troubleshooting why our procedure (or the one listed in this tip) used to work in 2005 but now fails in 2008 after the upgrade is a pain.

Has anyone found a way in powershell or some other means to find the physical path to DTSEXEC for the instance you are running on? You can't hard code the path to C: because it may not be installed there with custom installations of SQL Server.

Monday, October 18, 2010 - 10:59:24 AM - Suresh Channamraju

Or after executing the SSIS Package, disable xp_cmdshell

Will this not work?

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO

-- To update the currently configured value for advanced options.
RECONFIGURE
GO

-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO

-- To update the currently configured value for this feature.
RECONFIGURE
GO

EXEC dbo.usp_LaunchSSIS 'E:\SSIS\Packages\', 'MSSQLTIPS01', 'Foo_Dev' '[email protected]';
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.
RECONFIGURE
GO

-- To disable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO

-- To update the currently configured value for this feature.
RECONFIGURE
GO