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:
