Call SSIS Package from the Stored Procedure

Enable the xp_cmdshell option, 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.

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)

Below is the script to execute 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[User::varTargetName].Properties[Value];"c:\dtsxTest\Test2.txt"'

----now making "dtexec" SQL from dynamic values

set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
--- to execute package from file path use below query
---EXEC xp_cmdshell 'dtexec /f "E:\PackageLocation\WellZoneExtraction.dtsx"'

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

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.