Hi everybody,

It has been a long time since my previous post about mapping stored procedure parameters to SSIS package variables post.
I have been busy+lazy and never get around to posting a follow-up post until now.

In this post, I will show how to execute a SSIS package using the dtexec Utility from a Stored Procedure because it is one of the most frequent questions I received for my previous blog post.

First of all, I have a few important things to let you know.
I am a Senior .NET Developer and Database Administration is not my area of expertise.

Therefore, questions about Database Server configuration and permissions etc, I won't be able to answer all of them.
It will be faster to get the answer if you ask your Database Administrator if something is not working due to Server permissions and settings.

Here is the Stored Procedure signature I will use.

CREATE PROCEDURE [dbo].[pr_SSISDriver]
	-- This can be a network address like this ===> '\\serverName\d$\MySsis\mySsis.dtsx'
	@ssisPkgFilePath varchar(500),

	-- You can store any kind of configuration values in the SSIS Package DTS Config file.
	-- This can be a network address like this ===> '\\serverName\d$\MySsis\myconfig.dtsConfig'
	@ssisPkgConfigFilePath varchar(500),

	-- You can pass in currently logged-in User Id (if any) as a VarChar string in case you need to do something with it
	@currentUser varchar(200),

	-- My SSIS package dumps out an Excel file.
	-- This can be a network address like this ===> '\\serverName\d$\MySsisOutputFolder\'
	@outputFolderLocation varchar(500),

	-- My SSIS package sends out an email notification after successfully processing everything.
	-- You can just use the DTS Configuration file to store this (or) you can pass this in as a SSIS Package Variable.
	@smtpServer varchar(100),
	@sendEmailFrom varchar(100),

	-- pass in email addresses comma-delimitted like this ===> 'a@blah.com, b@blah.com'
	@sendSuccessEmailTo varchar(200),
	@sendErrorEmailTo varchar(200),

	-- pass in Date input fields as a string ===> 'MM/dd/yyyy'
	@mySampleInputDate varchar(10)
AS
BEGIN
	-- ================================================================================
	-- (1) Validate Input Variable values
	--     Perform the rest of the input paramater validations on your own
	-- ================================================================================
	IF nullif(ltrim(rtrim(@ssisPkgFilePath)), '') is null
	begin
	--{
		RAISERROR( 'Invalid ''ssisPkgFilePath'' value' /*Message*/, 16 /*Severity*/, 1 /*State (arbitary number)*/ )
	--}
	end

	-- ================================================================================
	-- (2) REMOVE trailing semi-colons from email address
	-- ================================================================================
	WHILE @sendErrorEmailTo like '%;'
	begin
		set @sendErrorEmailTo = LEFT(@sendErrorEmailTo, LEN(@sendErrorEmailTo)-1)
	end

	-- ================================================================================
	-- (3) Build the string to execute the SSIS package.
	-- ================================================================================
	DECLARE @dtsExecCmd varchar(4000) -- varchar(8000) is the max length allowed for "varchar"

	SET @dtsExecCmd = 'dtexec /F "' + ltrim(rtrim(@ssisPkgFilePath)) + '"'
	SET @dtsExecCmd = @dtsExecCmd + ' /CONFIGFILE "' + @ssisPkgConfigFilePath + '"' -- SSIS package config file for Database Connection
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::outputFolderLocation].Properties[Value]";"\"'        + @outputFolderLocation               + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::currentUser].Properties[Value]";"\"'                 + @currentUser                        + '\""'

	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::smtpServer].Properties[Value]";"\"'                  + @smtpServer                         + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::sendEmailFrom].Properties[Value]";"\"'               + @sendEmailFrom                      + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::sendSuccessEmailTo].Properties[Value]";"\"'          + @sendSuccessEmailTo                 + '\""'
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::sendErrorEmailTo].Properties[Value]";"\"'            + @sendErrorEmailTo                   + '\""'

	-- Pass in the Date field as a VARCHAR string data type
	-- the corresponding SSIS package variable ****should be**** getting the proper Date value
	SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::myInputDate].Properties[Value]";"\"'                 + @mySampleInputDate                  + '\""'

	PRINT 'Print DTEXEC Command for debugging purposes: ' + @dtsExecCmd + '
'


	-- ================================================================================
	-- (4) Run the SSIS package by building the DTSEXEC command line
	-- ================================================================================
	-- Variable to hold the RETURN code from the DTEXEC utility
	DECLARE @result INT

	-- Declare TEMP table variable to hold the output messages from the SSIS package
	DECLARE @output TABLE( [ssisOutput] varchar(max) )
	INSERT INTO @output
	--{
		EXEC @result = master..xp_cmdshell @dtsExecCmd
	--}

	PRINT 'DTEXEC Execute Result = ' + cast(@result as varchar(10))


	-- ================================================================================
	-- (5) Check if any error occured
	-- ================================================================================
	IF (@result is not null and @result<>0) OR EXISTS(select 1 from @output where ssisoutput like '%error%')
	begin
	--{
		PRINT 'SSIS package ''' + @ssisPkgFilePath + ''' failed.'

		-- ================================================================================
		-- (6) Return Error Rows so we know there were errors while executing the SSIS pkg
		-- ================================================================================
		SELECT * FROM @output

		-- ================================================================================
		-- (7) Perform error logging as needed (if you want to).
		-- ================================================================================
	--}
	end


	SET NOCOUNT OFF;

	-- RETURN code from the DTEXEC utility
	RETURN @result;
END

Okay, the SQL Stored Procedure code is pretty self-explanatory.
The only elaboration I have to make is the section #5.

Basically, the dtexec command line utility prints out the SSIS package execution messages to the Console.
Each line gets returned into the stored procedure as "rows".
I am filtering those "rows" by looking for anywhere in the line with "error" string so I only get error statements.

You are probably going to ask how do I detect errors from my Client Application that executes this Stored Procedure.
That is what the section #6 is for.
You just check whether the stored procedure returned a row or not.
If it returns a row, that means there was an error while executing the stored procedure.

You might ask, well the SSIS has its own error-handling features. Why can't you just use it?
The answer is simple. You might need to display that there was an error while processing the user's request in your Web/Windows application.
The only way to know there was an error is to check if the stored procedure returned a result set.

I am not an expert at SSIS and this is my solution of running a SSIS package from a Stored Procedure.
There might be better solutions out there, but this solution works out well for me.

I hope this post is really helpful to many people out there.

Cheers,
Soe