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.
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:
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" 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:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
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:
Here, I have used two special commands, one is "
xp_cmdshell" and the second is "
Now what is "
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)
set @packagename = 'ImportItemFile'
set @servername = 'myserver\sql2k5'
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 @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
set @ssisstr = @ssisstr + @params
DECLARE @returncode int
EXEC @returncode = xp_cmdshell @ssisstr
Now we will see the variable passing structure of 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.
|Value ||Description |
|0 ||The package executed successfully. |
|1 ||The package failed. |
|3 ||The package was cancelled by the user. |
|4 ||The utility was unable to locate the requested package. The package could not be found. |
|5 ||The utility was unable to load the requested package. The package could not be loaded. |
|6 ||The utility encountered an internal error of syntactic or semantic errors in the command line. |
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.
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:
- 21st June, 2008: Initial post