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'
-- 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'
-- You can pass in currently logged-in User Id (if any) as a VarChar string in case you need to do something with it
-- My SSIS package dumps out an Excel file.
-- This can be a network address like this ===> '\\serverName\d$\MySsisOutputFolder\'
-- 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.
-- pass in email addresses comma-delimitted like this ===> 'firstname.lastname@example.org, email@example.com'
-- pass in Date input fields as a string ===> 'MM/dd/yyyy'
-- (1) Validate Input Variable values
-- Perform the rest of the input paramater validations on your own
IF nullif(ltrim(rtrim(@ssisPkgFilePath)), '') is null
RAISERROR( 'Invalid ''ssisPkgFilePath'' value' /*Message*/, 16 /*Severity*/, 1 /*State (arbitary number)*/ )
-- (2) REMOVE trailing semi-colons from email address
WHILE @sendErrorEmailTo like '%;'
set @sendErrorEmailTo = LEFT(@sendErrorEmailTo, LEN(@sendErrorEmailTo)-1)
-- (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%')
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).
SET NOCOUNT OFF;
-- RETURN code from the DTEXEC utility
Okay, the SQL Stored Procedure code is pretty self-explanatory.
The only elaboration I have to make is the section #5.
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.