How to call SSIS package from the stored procedure

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 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 Download enablexp_cmdScript.zip - 251 B Download enablexp_cmdScript.zip - 251 B

2) ssisfromsql.sql Download ssisfromsql.zip - 570 B Download ssisfromsql.zip - 570 B

First, i will tell the other way to call SSIS package other tahn 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 two way, 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 meny select your instance name and click on "xp_cmdshell" option under it, just like this,



just enable the xp_cmdshell option, the xp_cmdsheel 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 you 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, it will fetche the Excel file from the provided location on Server, and will load all the items from excel file to Item table in database.

Varialbes i have to pass are: FileName, CreatedBy, ContractDbConnectionString, BatchID, SupplierID

Here, i have used two special command one is "xp_cmdshell" and 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: MSDN)

Now the Script i will create here, is dynamic SQL, means we can use it to call any SSIS pacakges, just we have to pass necessary varibales.

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 = "myserversql2k5"


---- 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.1Common
SSISNewItem.xls\127.0.0.1Common
SSISNewItem.xls"" /set package.variables[CreatedBy].Value;
""Chirag"" /set package.variables[ContractDbConnectionString].Value;
""Data Source=myserverSQL2K5;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 varification
--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 packageDataFlowTask.Variables[User::MyVariable].Value;newValue

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

Value Description

0 -- The package executed successfully.

1 -- The package failed.

3 -- The package was canceled 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, by 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 during calling of SSIS from Web service.

Resources

"xp_cmdshell" and "dtexec" also can be used for many more functionality, following are the links for both command which will describe both in details for their syntax and usage.

dtexec : MSDN
xp_cmdshell : Database Journal