Get return/exit code from a package executed from a SSIS catalogue

Question

I’m new to SSIS/SQL so the following naming references might be incorrect but I hope to convey the gist of the issue

The scheduling tool I’m using executes deployed SSIS packages in SQL 2012 and this tool needs to indicate when a SSIS package fails and then stop running any subsequent scheduled jobs.

The project uses the SSIS project deployment model in SQL 2012. The deployed SSIS packages are then called by a 3rd party scheduling tool. In the SSIS catalogue we use the Execute option to generate a SQL script to pass to the scheduler. This script is edited to add a parameter to ensure that the job runs SYNSCHRONOUSLY (i.e. the caller keeps waiting till the job is finished). The SQL script is run from the scheduling tool and will only move to the next job upon completion.

The issue is that the scheduling tool is not receiving a return code when the SSIS package fails. If a SSIS package fails it contains a step to capture and send an email with error notification, hence we do have a view on failures. However any dependant jobs in the scheduling flow are also run irrespective of whether the job has completed successfully or not. Is there a parameter to force a return code to be sent to the 3rd part scheduling tool?

Example of the script being used to execute the package:

*Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Extract_Job.dtsx',  @execution_id=@execution_id OUTPUT, @folder_name=N'ETL', @project_name=N'ETL', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'SYNCHRONIZED',  @parameter_value= 1; -- turn on synchronized execution
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO*

Things I’ve tried

• Using the DTSEXEC command. This however doesn’t send a return code and also runs synchronously and it needs to run synchronously
• The 3rd party scheduler has a plugin for the SSIS catalogue but has an unresolved bug so isn’t working on the current version

Solution 1

While using T-SQL to execute the SSIS package does seem to work however it seems DTExec is still more suitable and stable. I found using the new version of DTExec does have an option (poorly documented) to pass a parameter to allow synchronous processing. Using this method also allows the exit code to be returned which is the problem I was experiencing with the T-SQL option.

Example of script:

DTExec.exe /ISSERVER "\SSISDB\project\package_name.dtsx" /SERVER "server_name" /Par "$ServerOption::SYNCHRONIZED(Boolean)";True

The older version of dtexec will not support the new options, ensure that you're executing the new version if both are available on the server. To check which version is in the search path (default) use "WHERE DTEXEC". Type "DTEXEC /?" and check if the new options appear in the help text, if not, prefix the command execution with the correct path location.

New version location

C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

Older version location

C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

Solution 2

Made mine SYNCHRONOUS within the stored procedure. Used the following

EXEC [SSISDB].[catalog].[create_execution]
@package_name = N'FixProductType.dtsx', 
@execution_id = @execution_id OUTPUT, 
@folder_name = N'BI', 
@project_name = N'DataCleaning',
@use32bitruntime = False;

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
@execution_id, 
@object_type = 50, 
@parameter_name = N'LOGGING_LEVEL', 
@parameter_value = 1;

EXEC [SSISDB].[catalog].[start_execution] @execution_id;

DECLARE @status AS BIGINT = 1;
WHILE(@status = 1 OR @status = 2 OR @status = 5 OR @status= 8)
BEGIN
PRINT @status
PRINT 'waiting 5 seconds for Package to finish'
WAITFOR DELAY '00:00:5';

SET @status = (SELECT [Status] FROM SSISDB.[catalog].[executions]
        WHERE execution_id = @execution_id);
END

Solution 3

Can you use the [catalog].[executions] view and the @execution_id as explained in this post:

http://sqlblog.com/blogs/jamie_thomson/archive/2011/07/16/ssis-logging-in-denali.aspx

to get the execution result?

Normally I would expect that the start_execution stored procedure would return a result, but the doco doesn't indicate this. Can't hurt to try though.

DECLARE @Result INT
EXEC @Result = [SSISDB].[catalog].[start_execution] @execution_id
SELECT @Result