catalog.set_execution_parameter_value(SSISDB 数据库)catalog.set_execution_parameter_value (SSISDB Database)

适用范围:SQL Server,包括 Linux Azure SQL 数据库yesAzure SQL 数据仓库no并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Integration ServicesIntegration Services 目录中的执行实例设置参数的值。Sets the value of a parameter for an instance of execution in the Integration ServicesIntegration Services catalog.

当已开始执行实例后,就不能再更改参数值。A parameter value cannot be changed after an instance of execution has started.

语法Syntax

catalog.set_execution_parameter_value [ @execution_id = execution_id  
    , [ @object_type = ] object_type  
    , [ @parameter_name = ] parameter_name  
    , [ @parameter_value = ] parameter_value  

参数Arguments

[ @execution_id = ] execution_id [ @execution_id = ] execution_id
执行实例的唯一标识符。The unique identifier for the instance of execution. execution_id 为 bigint 。The execution_id is bigint.

[ @object_type = ] object_type[ @object_type = ] object_type
参数的类型。The type of parameter.

对于以下参数,请将 object_type 设置为 50For the following parameters, set object_type to 50

  • LOGGING_LEVELLOGGING_LEVEL

  • CUSTOMIZED_LOGGING_LEVELCUSTOMIZED_LOGGING_LEVEL

  • DUMP_ON_ERRORDUMP_ON_ERROR

  • DUMP_ON_EVENTDUMP_ON_EVENT

  • DUMP_EVENT_CODEDUMP_EVENT_CODE

  • CALLER_INFOCALLER_INFO

  • SYNCHRONIZEDSYNCHRONIZED

使用值 20 表示项目参数,使用值 30 表示包参数。Use the value 20 to indicate a project parameter or the value 30 to indicate a package parameter.

object_typesmallintThe object_type is smallint.

[ @parameter_name = ] parameter_name[ @parameter_name = ] parameter_name
参数名。The name of the parameter. parameter_name 为 nvarchar(128) 。The parameter_name is nvarchar(128).

[ @parameter_value = ] parameter_value[ @parameter_value = ] parameter_value
参数的值。The value of the parameter. parameter_value 为 sql_variant 。The parameter_value is sql_variant.

RemarksRemarks

要查找用于给定执行的参数值,请查询 catalog.execution_parameter_values 视图。To find out the parameter values that were used for a given execution, query the catalog.execution_parameter_values view.

若要指定在包执行期间记录的信息范围,请将 parameter_name 设置为 LOGGING_LEVEL 并将 parameter_value 设置为以下值之一。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.

object_type 参数设置为 50。Set the object_type parameter to 50.

ReplTest1Value 描述Description
00 NoneNone

关闭日志记录。Logging is turned off. 仅记录包执行状态。Only the package execution status is logged.
11 “基本”Basic

除了自定义事件和诊断事件之外,记录其余所有事件。All events are logged, except custom and diagnostic events. 这是默认值。This is the default value.
22 性能Performance

仅记录性能统计信息、OnError 和 OnWarning 事件。Only performance statistics, and OnError and OnWarning events, are logged.
33 “详细”Verbose

记录所有事件,包括自定义事件和诊断事件。All events are logged, including custom and diagnostic events.
自定义事件包括 Integration Services 任务记录的那些事件。Custom events include those events that are logged by Integration Services tasks. 有关详细信息,请参阅日志记录的自定义消息For more information, see Custom Messages for Logging
44 运行时沿袭Runtime lineage

收集跟踪数据流中的沿袭所需的数据。Collects the data required to track lineage in the data flow.
100100 自定义日志记录级别Custom logging level

指定 CUSTOMIZED_LOGGING_LEVEL 参数中的设置。Specify the settings in the CUSTOMIZED_LOGGING_LEVEL parameter. 有关可指定的值的详细信息,请参阅 catalog.create_customized_logging_levelFor more info about the values that you can specify, see catalog.create_customized_logging_level.

有关自定义日志记录级别的详细信息,请参阅在 SSIS 服务器上启用包执行的日志记录For more info about customized logging levels, see Enable Logging for Package Execution on the SSIS Server.

若要指定 Integration Services 服务器在包执行期间出现任意错误时生成转储文件,请为未运行的执行实例设置以下参数值: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.

参数Parameter ReplTest1Value
execution_idexecution_id 执行实例的唯一标识符The unique identifier for the instance of execution
object_typeobject_type 5050
parameter_nameparameter_name 'DUMP_ON_ERROR'DUMP_ON_ERROR
parameter_valueparameter_value 11

若要指定 Integration Services 服务器在包执行期间出现事件时生成转储文件,请为未运行的执行实例设置以下参数值: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.

参数Parameter ReplTest1Value
execution_idexecution_id 执行实例的唯一标识符The unique identifier for the instance of execution
object_typeobject_type 5050
parameter_nameparameter_name 'DUMP_ON_EVENT'DUMP_ON_EVENT
parameter_valueparameter_value 11

若要指定在包执行期间导致 Integration Services 服务器生成转储文件的事件,请为未运行的执行实例设置以下参数值: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.

参数Parameter ReplTest1Value
execution_idexecution_id 执行实例的唯一标识符The unique identifier for the instance of execution
object_typeobject_type 5050
parameter_nameparameter_name DUMP_EVENT_CODEDUMP_EVENT_CODE
parameter_valueparameter_value 一个或多个事件代码One or more event codes

示例Example

以下示例指定 Integration Services 服务器在包执行期间出现任意错误时生成转储文件: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  

示例Example

以下示例指定 Integration Services 服务器在包执行期间出现事件时生成转储文件,并指定导致该服务器生成这些文件的事件: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  

返回代码值Return Code Value

0(成功)0 (success)

结果集Result Sets

NoneNone

权限Permissions

此存储过程需要下列权限之一:This stored procedure requires one of the following permissions:

  • 针对执行实例的 READ 和 MODIFY 权限READ and MODIFY permissions on the instance of execution

  • ssis_admin 数据库角色的成员资格Membership to the ssis_admin database role

  • sysadmin 服务器角色的成员资格Membership to the sysadmin server role

错误和警告Errors and Warnings

下面的列表描述了一些可能引发错误或警告的情况: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

另请参阅See Also

catalog.execution_parameter_values(SSISDB 数据库) catalog.execution_parameter_values (SSISDB Database)
生成包执行的转储文件Generating Dump Files for Package Execution