catalog.set_execution_parameter_value (SSISDB Database)

[ @execution_id = ] execution_id

The unique identifier for the instance of execution. The execution_id is bigint.

[ @object_type = ] object_type

The type of parameter.

For the following parameters, set object_type to 50

• LOGGING_LEVEL

• DUMP_ON_ERROR

• DUMP_ON_EVENT

• DUMP_EVENT_CODE

• CALLER_INFO

• SYNCHRONIZED

Use the value 20 to indicate a project parameter or the value 30 to indicate a package parameter.

The object_type is smallint.

[ @parameter_name = ] parameter_name

The name of the parameter. The parameter_name is nvarchar(128).

[ @parameter_value = ] parameter_value

The value of the parameter. The parameter_value is sql_variant.

To find out the parameter values that were used for a given execution, query the catalog.execution_parameter_values view.

To specify the scope of information that is logged during a package execution, set parameter_name to LOGGING_LEVEL and set parameter_value to one of the following values.

Set the object_type parameter to 50.

To specify that the Integration Services server generates dump files when any error occurs during a package execution, set the following parameter values for an execution instance that hasn’t run.

To specify that the Integration Services server generates dump files when events occur during a package execution, set the following parameter values for an execution instance that hasn’t run.

To specify the events during package execution that cause the Integration Services server to generate dump files, set the following parameter values for an execution instance that hasn’t run. Separate multiple event codes using a semi-colon.

The following example specifies that the Integration Services server generates dump files when any error occurs during a package execution.

exec catalog.create_execution  'TR2','Recurring ETL', 'Dim_DCVendor.dtsx',NULL, 0,@execution_id out
exec catalog.set_execution_parameter_value  @execution_id, 50, 'DUMP_ON_ERROR',1

The following example specifies that the Integration Services server generates dump files when events occur during a package execution, and specifies the event that causes the server to generate the files.

exec catalog.create_execution  'TR2','Recurring ETL', 'Dim_DCVendor.dtsx',NULL, 0,@execution_id out
exec catalog.set_execution_parameter_value  @execution_id, 50, 'DUMP_ON_EVENT',1
declare @event_code nvarchar(50)
set @event_code = '0xC020801C'
exec catalog.set_execution_parameter_value  @execution_id, 50, 'DUMP_EVENT_CODE', @event_code

0 (success)

None

This stored procedure requires one of the following permissions:

• READ and MODIFY permissions on the instance of execution

• Membership to the ssis_admin database role

• Membership to the sysadmin server role

The following list describes some conditions that may raise an error or warning:

• The user does not have the appropriate permissions

• The execution identifier is not valid

• The parameter name is not valid

• The data type of the parameter value does not match the data type of the parameter

Reference

Concepts