Capture SSIS Package Execution Status

Executing an SSIS package from within a stored procedure is not an uncommon request.  People sometimes scratch their head when it comes to figuring out if that package ran successfully or not though.  There may be a process that runs a stored procedure and kicks off a certain SSIS package depending on the situation.  Maybe there are parameters that were used in the stored procedure that need to be passed to the SSIS package.  Either way you’ll want to know if the package executed successfully or not.  This isn’t an issue if you are running the stored procedure and can see the output results inside management studio or you have some logging built into your packages that can be looked at someplace else at any time.  So what if you need to know if the package ran successfully or not right away, inside your stored procedure.  Maybe there is a second package that needs to run or other processing that needs to occur based on the package running successfully or failing.
Luckily the output from DTEXEC can be captured inside a stored procedure.  When calling an SSIS package from a stored procedure you will be using DTEXC in combination with xp_cmdshell (which is not enabled by default in SQL Server).  DTEXEC will return what is called the exit code tell the execution status in the form of a number ranging from 0 to 6.  The exit codes are defined as follows:

0	The package executed successfully.
1	The package failed.
3	The package was canceled by the user.
4	The utility was unable to locate the requested package. The package could not be found.
5	The utility was unable to load the requested package. The package could not be loaded.
6	The utility encountered an internal error of syntactic or semantic errors in the command line.

We need to know if the exit code is a zero (successfully ran) or not.  Simply define a variable that the value can be stored in and use EXEC to execute the package and store the value.  The first method pertains to hard coding the execution commands.  Declare a variable for the code to be stored in followed by executing the package.  This will store the value of the exit code into the variable @ExitCode, which you can then select if you want to see it or use that variable in other ways for error handling.
DECLARE @ExitCode int
EXEC @ExitCode = xp_cmdshell ‘DTEXEC /F “c:SSISPackage.dtsx”‘
SELECT @ExitCode
Things get a little different when the package value is stored in a variable.  Again, declare the variable for the exit code.
DECLARE @ExitCode int
Next we need to build the dtexec portion of the statement.  Assuming you want to make this dynamic we will build the complete command and store it in a variable called @SQL.  You could just store all of this code straight into the variable @SQL, but for demonstration purpose I will show you how you can combine different portions of the DTEXEC command.
DECLARE @DTEXEC varchar(20), @SQL varchar(500), @PackagePath varchar(50)
Once you have your variables declared set their values as needed throughout the stored procedure.
SET @DTEXEC = ‘DTEXEC /F ‘
SET @PackagePath = ‘”c:SSISPackage.dtsx”‘
SET @SQL = @DTEXEC + @PackagePath
If you print the new @SQL Code you will notice a difference between this DTEXEC statement and the one we hard coded.
PRINT @SQL à DTEXEC /F “c:SSISPackage.dtsx”
Before -> ‘DTEXEC /F “c:SSISPackage.dtsx”‘
The new code is missing the apostrophe on the beginning and end of the statement.  These are not necessary when the statement is stored in a variable and if they are there the process will fail.  Finally, execute the commands together and select your exit code.
EXEC @ExitCode = xp_cmdshell @SQL
SELECT @ExitCode
Hopefully this sheds some light on capturing the outcome of an SSIS package using DTEXEC.  From this point you can use that value to do error handling, fire off other processes or log the code to a table as part of your SSIS logging so you have a record of your package executions.  There is a much less elegant method that involves storing all the package output into a table, but that is a discussion for another day.