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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips