.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

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

Posted By:Karthikeyan Anbarasan       Posted Date: April 05, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

In this article we are going to see how to execute a SSIS package inside a stored procedure in SQL Server Integration Services (SSIS)


In this article we are going to see how to execute a SSIS package inside a stored procedure. 


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,

    @strLocation VARCHAR(500),  
    @strServer VARCHAR(500),
    @strDbName VARCHAR(500),
    @EmailAddress VARCHAR(500)


@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
   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'

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

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


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend