In this article we are going to see how to execute a SSIS package inside a stored procedure. To follow my series of articles on SSIS packages refer to my profile for the links.
In this article since we are going to see how to execute the SSIS package using a stored procedure. We need to carry out some basic configuration. Go to SQL Server Surface area Configuration and select the Surface Area Configuration for features as shown in the images below.
Now to go xp_cmdshell tab and select the checkbox Enable xp_cmdshell.
Here we have enabled this in order to execute our SSIS Package using this procedure. We will use one of our packages that we have created as a sample in our existing articles. Now we are going to create a stored procedure to execute the package with passing some input variables to be used in the connection string as follows:
CREATE PROCEDURE USP_ExecuteSSIS We can then call the stored procedure as follows:
SET NOCOUNT ON
SELECT @EmailAddress = QUOTENAME(@EmailAddress,'"')
SELECT @strServer = QUOTENAME(@@servername,'"')
SELECT @Cmd = 'DTexec /FILE "' + @strLocation + 'MyProject.dtsx"
/MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW'
+ ' /SET \Package.Variables[User::varSourceSQLServer].Properties[Value];' + @strServer
+ ' /SET \Package.Variables[User::varErrorNotifyEmail].Properties[Value];' + @EmailAddress
EXEC @ReturnCode = xp_cmdshell @Cmd
IF @ReturnCode <> 0
SELECT @Msg = 'SSIS package execution failed - ' + @strLocation
+ 'INstance Name: ' + @strServer + '.' + @strDbName
EXEC msdb.dbo.sp_send_dbmail @recipients = @EmailAddress ,
@body = @Msg, @subject = 'SSIS Execution Failure'
EXEC USP_ExecuteSSIS 'C:\Packages\', 'KARTHIK-PC/Karthik', 'MyProject' 'MyMail@gmail.com';