Floating FB popout byF5debug

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

 

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.

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

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.

clip_image001

Now to go xp_cmdshell tab and select the checkbox Enable xp_cmdshell.

clip_image002

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 <> 0BEGIN 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 @ReturnCodeGO

We can then call the stored procedure as follows,

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

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter