SQL Server Integration Services (SSIS) – Part 20 – Executing SSIS package from Stored Procedure

KarthikAnbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 11102 red flag

This is part 20 of the series of article on SSIS. In this article we shall learn how to execute SSIS package from stored procedure.

Introduction

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 the below URL’s 

SSIS Series Part 1 to 19

Steps:


In this article since we are going to see on executing 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 below images.



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 our package which we have created as 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  
    @strLocation VARCHAR(500),  
    @strServer VARCHAR(500),
    @strDbName VARCHAR(500),
    @EmailAddress VARCHAR(500)
AS

SET NOCOUNT ON

DECLARE
@Cmd VARCHAR(4000),
@ReturnCode INT,
@Msg VARCHAR(1000)

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
BEGIN
   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'
END

RETURN @ReturnCode
GO
We can then call the stored procedure as follows,

EXEC USP_ExecuteSSIS 'C:\Packages\', 'KARTHIK-PC/Karthik', 'MyProject' 'MyMail@gmail.com';
Thanks !

Page copy protected against web site content infringement by Copyscape

About the Author

KarthikAnbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)