Execute SSIS package from Stored Procedure using xp_cmdshell

Packages help in executing many different tasks at once and there are times when there are functionality requirement where package needs to run on a particular event invoked by the end user and we want to handle this by calling a stored procedure. Below is a small demonstration as how we accomplish this scenario using stored procedure using xp_cmdshell.

Say I have a package that unions 3 different flat files and produces .xls file, this file is then conditionally split and one file is sent to group of people. Let's call our package as "Splitpackage".

First thing is that we copy the command line code as below (note that I have imported the package in SQL server):

2. Click on Run as package and select command line from options on the left. Copy the code as we would need it in our procedure.

.3. Final step is creating a procedure that executes the command line.

 

CREATE PROCEDURE [dbo].[GenerateReport]

AS

BEGIN

    

    SET NOCOUNT ON;

 

 

declare @execpkg varchar(8000)

declare @cmdline varchar(8000)

 

 

set @cmdline =' /DTS "\MSDB\Data Collector\SplitPackage" /SERVER "DevTECHIE-PC" /CHECKPOINTING OFF /REPORTING V '

set @execpkg = 'dtexec '

set @execpkg = @execpkg + @cmdline

 

 

DECLARE @returncode int

EXEC @returncode = xp_cmdshell @execpkg

select @returncode

END

 

GO

 

Tags: , , , ,

SQL Server | SQL Tricks | SSIS