How to Call SSIS Package from the Stored Procedure

• Download enablexp_cmdScript - 251 B
• Download ssisfromsql - 570 B

Introduction

SSIS (SQL Server Integration Services) packages are the server side packages which will be called from the server, that may be achieved by creating a web service. But sometimes, we want to pass some Excel, or flat files in SSIS package, and this file must be transferred to server to use in SSIS package.

So sometimes, there may be some security issues when the web service will be restricted to allow using resources on the server. So we have to use some other way, not web service, to call SSIS.

Background

This article assumes that you are familiar with creating SSIS packages and how to add variables into package and how to call SSIS package to use in code.

Using the Code

This article has two attached files:

1. enablexp_cmdScript.sql
2. ssisfromsql.sql

First, I will tell the other way to call SSIS package other than using "web service". We can use Stored procedure to call SSIS package. How?

There is one System Stored Procedure in SQL Server 2005 called "xp_cmdshell" which will be set to "False", means this sp is not active by default at the time of SQL Server Installation. We have to manually enable this SP to use. This can be done in two ways, either by running some script, (which is given in enablexp_cmdscript.sql file) or by using "SQL Server surface Area configuration" tool which will be installed with SQL Server 2005.

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.

Start the Surface Area congifuration tool from your SQL server installation in Program Menu. It will look like this:

Now, click on the "Surface Area configuration for Features" link and you will see the following screen. From the Left side menu, select your instance name and click on "xp_cmdshell" option under it, just like this:

Just enable the xp_cmdshell option, the xp_cmdshell SP will be enabled after you restart the SQL server services.

If you do not want to do like this, just run the following script lines in your selected instance in SQL Server:

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        

Now, we are ready to use "xp_cmdshell" stored procedure to call our SSIS package.

Now, I have created one SSIS package called "ImportItemFile", what it will do is it will fetch the Excel file from the provided location on Server, and will load all the items from Excel file to Item table in database.

Variables I have to pass are: FileName, CreatedBy, ContractDbConnectionString, BatchID, SupplierID.

Here, I have used two special commands, one is "xp_cmdshell" and the second is "dtexec".
Now what is "dtexec" command.

dtexec: The dtexec command prompt utility is used to configure and execute SQL Server 2005 Integration Services (SSIS) packages. The dtexec utility provides access to all the package configuration and execution features, such as connections, properties, variables, logging, and progress indicators. The dtexec utility lets you load packages from three sources: a Microsoft SQL Server database, the SSIS service, and the file system.
(Reference from: http://msdn.microsoft.com/en-us/library/ms162810.aspx)

Now the script I will create here is dynamic SQL, means we can use it to call any SSIS packages, we only have to pass the necessary variables.

declare @ssisstr varchar(8000), @packagename varchar(200),@servername varchar(100)
declare @params varchar(8000)
----my package name
set @packagename = 'ImportItemFile'
----my server name
set @servername = 'myserver\sql2k5'

---- please make this line in single line, I have made this line in multiline 
----due to article format.
----package variables, which we are passing in SSIS Package.
set @params = '/set \package.variables[FileName].Value;"\"\\127.0.0.1\Common
           \SSIS\NewItem.xls\"" /set \package.variables[CreatedBy].Value;
           "\"Chirag\"" /set \package.variables[ContractDbConnectionString].Value;
           "\"Data Source=myserver\SQL2K5;User ID=sa;Password=sapass;
           Initial Catalog=Items;Provider=SQLNCLI.1;Persist Security Info=True;
           Auto Translate=False;\"" /set \package.variables[BatchID].Value;"\"1\"" 
           /set \package.variables[SupplierID].Value;"\"22334\""'

----now making "dtexec" SQL from dynamic values
set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
set @ssisstr = @ssisstr + @params
-----print line for verification 
--print @ssisstr

----
----now execute dynamic SQL by using EXEC. 
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
select @returncode

Now we will see the variable passing structure of the "dtexec" command:

/SET \package\DataFlowTask.Variables[User::MyVariable].Value;newValue

Now the @returncode variable will be returned by the "dtexec" command and it will be two record sets, the first will return the code from the following possible value which will indicate the SSIS package status, and the second table will describe all the processes that happened during execution of the SSIS package.

So, in this way, we can call the SSIS package from the Stored Procedure by using "xp_cmdsjell" and "dtexec" command from the SQL Server. And we will never face the problems which we may get while calling SSIS from Web service.

Resources

"xp_cmdshell" and "dtexec" can also be used for much more functionality. Following are the links for both commands which will describe both in detail for their syntax and usage:

• dtexec: http://msdn.microsoft.com/en-us/library/ms162810.aspx
• xp_cmdshell: http://www.databasejournal.com/features/mssql/article.php/3372131

History

• 21^st June, 2008: Initial post