Quick Tip – Run SSIS 2012 packages synchronously and other execution parameters

In the new SSIS 2012 catalog’s project deployment model, by default if you execute a package with the Execute Package control flow task, or through the new API’s start_execution, the call will be made asynchronously.

In order to call the package synchronously, and therefore keep the caller waiting until the package finishes, you have to set a parameter value to true. This parameter, SYNCHRONIZED, is very, very sparsely documented on the ‘Net, but you can see it here assuming you’ve already executed some packages in your catalog:

SELECT DISTINCT parameter_name
  FROM SSISDB.catalog.execution_parameter_values  
 WHERE object_type = 50 -- 50 == execution parameter

To call it, simply call catalog.set_execution_parameter_values before calling catalog.start_execution and set the SYNCHRONIZED parameter to true (1). Like so:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  -- execution_id from catalog.create_execution
        @object_type=50, 
        @parameter_name=N'SYNCHRONIZED', 
        @parameter_value= 1; -- turn on synchronized execution

While I’m here, the other execution parameters are as follows:

• DUMP_ON_EVENT
• DUMP_EVENT_CODE
• LOGGING_LEVEL
• CALLER_INFO
• DUMP_ON_ERROR

DUMP_ON_EVENT – Boolean – 0* | 1 – Used with DUMP_EVENT_CODE, this signals to the SSIS engine to create a dump when the specified DUMP_EVENT_CODEs are encountered.

DUMP_EVENT_CODE – String – “”* – Used with DUMP_ON_EVENT, a semi-colon delimited list of event codes that will generate dump files if encountered. If we wanted to capture login failures and unable to connect event codes, specify respectively: 0x80040E4D;0×80004005.

LOGGING_LEVEL – Integer – 0 | 1* | 2 | 3 – Sets the logging level for this execution. The values represent no logging, basic, performance, and verbose levels respectively.

CALLER_INFO – String – “”* – Used to pass-in additional information on the caller. Helpful when viewing the overview of an execution in the built-in reporting provided with the catalog.

DUMP_ON_ERROR – Boolean – 0* | 1 – If an error is encountered, create a dump.

* – default value

See below for a complete example of the above parameters.

DECLARE @execution_id BIGINT = 0;

-- Create a package execution
EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Package.dtsx', 
        @execution_id=@execution_id OUTPUT, 
        @folder_name=N'PhilsTest', 
        @project_name=N'Demo', 
        @use32bitruntime=False;

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

-- Set our package parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_ON_EVENT', 
        @parameter_value=1; -- true

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'DUMP_EVENT_CODE', 
        @parameter_value=N'0x80040E4D;0x80004005';

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

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id,  
        @object_type=50, 
        @parameter_name=N'CALLER_INFO', 
        @parameter_value= N'Phil''s Demo';

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

-- Start the package
EXEC [SSISDB].[catalog].[start_execution] 
        @execution_id;

To confirm that they were accepted, here is a screenshot from the execution overview report: