Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server

How to Call SSIS Package from the Stored Procedure

Rate me:
Please Sign up or sign in to vote.
4.50/5 (9 votes)
20 Jun 2008GPL34 min read 295.9K   5.3K   39   17
No need to create web service for SSIS package call

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:

SurfaceAreaMain.JPG

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:

xp_cmdshell.JPG

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:

SQL
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.

SQL
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.

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.

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:

History

  • 21st June, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)


Written By
Software Developer
India India
Chirag Patel, a Programmer Analyst in a well known IT company working on .NET Technologies since last 2 years. He is interested in Pure business logic and fuzzy logic. his area of interest is in C#.NET, VB.NET and MSSQL 2005.

catch me on: http://groups.google.com/group/codewar

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 440820222-Jun-12 7:21
Member 440820222-Jun-12 7:21 
GeneralMy vote of 4 Pin
Member 906355610-Jun-12 21:42
Member 906355610-Jun-12 21:42 
Question2008 asp.net 4 ,visual stodio 2010 ,ssis package Pin
tretretretretr9-Oct-11 11:21
tretretretretr9-Oct-11 11:21 
GeneralMy vote of 5 Pin
tretretretretr5-Oct-11 22:23
tretretretretr5-Oct-11 22:23 
GeneralExecuting SSIS packages as job Pin
Sandor Gyarmati14-Jan-10 3:17
Sandor Gyarmati14-Jan-10 3:17 
Generalvariable Pin
trick_ie14-Jan-10 1:18
trick_ie14-Jan-10 1:18 
GeneralExecute SSIS using asp.net Pin
Member 347427624-Feb-09 8:19
Member 347427624-Feb-09 8:19 
GeneralCool Job, but... Pin
JanWaiz23-Jan-09 3:16
JanWaiz23-Jan-09 3:16 
GeneralGreat Article but One Small Suggestion Pin
Rick.Weyenberg6-Jan-09 4:48
Rick.Weyenberg6-Jan-09 4:48 
QuestionRe: Great Article but One Small Suggestion Pin
SuperLeftGiggleFoot15-Apr-09 6:33
SuperLeftGiggleFoot15-Apr-09 6:33 
QuestionHow I can execute SSIS package using c#.net (2.0) (using com object) ? Pin
Paresh Gujarathi10-Jul-08 21:59
Paresh Gujarathi10-Jul-08 21:59 
AnswerRe: How I can execute SSIS package using c#.net (2.0) (using com object) ? Pin
pvyaka0111-Jul-08 9:22
pvyaka0111-Jul-08 9:22 
GeneralRe: How I can execute SSIS package using c#.net (2.0) (using com object) ? Pin
Paresh Gujarathi11-Jul-08 23:31
Paresh Gujarathi11-Jul-08 23:31 
GeneralRe: How I can execute SSIS package using c#.net (2.0) (using com object) ? Pin
ivppjain10-Jan-09 6:22
ivppjain10-Jan-09 6:22 
GeneralDoes anyone know how to do this WITHOUT xp..cmdshell? Pin
Member 387927326-Jan-09 5:47
Member 387927326-Jan-09 5:47 
AnswerRe: Does anyone know how to do this WITHOUT xp..cmdshell? Pin
SuperLeftGiggleFoot15-Apr-09 6:23
SuperLeftGiggleFoot15-Apr-09 6:23 
GeneralRe: Does anyone know how to do this WITHOUT xp..cmdshell? Pin
Member 387927315-Apr-09 8:07
Member 387927315-Apr-09 8:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.