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

Answer 1

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