SSISDB5:使用TSQL脚本执行Package

2017-12-06 18:51:24来源:cnblogs.com作者:悦光阴人点击

分享
第七城市

通常情况下,ETL工程师创建一个Agent Job来周期性地执行Package,Agent底层调用SSISDB的存储过程(catalog.start_execution)以同步模式来启动执行实例,这就意味着,我们可以模拟Agent Job的行为,使用TSQL脚本执行Package。SSIS引擎每执行一次Package,都会创建一个执行实例(Execution Instance),每个执行实例都有唯一的标识 ExecutionID,数据类型是bigint。

执行实例可以包含参数,使用存储过程(catalog.set_execution_parameter_value)向执行实例传递参数。执行实例的参数分为两种类型:系统参数和Package参数,系统参数共有7个,用于控制日志记录、执行方式(异步/同步)等行为,而Package参数,用于向Package传递动态的参数值。为了控制执行实例的行为,需要向执行实例传递系统参数;有些Package包含参数,在开始执行实例之前,必须赋值。

一,创建执行实例

执行Package的第一步是创建Package的执行实例,并保存执行实例的ID。使用 catalog.create_execution 存储过程创建一个执行实例,创建成功之后,通过输出参数返回唯一的实例ID(Execution_ID)。

declare @Execution_ID bigintexec catalog.create_execution @package_name=N'Test.dtsx',    @Execution_ID=@Execution_ID output,    @folder_name=N'MyProjectFloder',    @project_name=N'MyProject',    @use32bitruntime=FALSE,    @reference_id=null

该SP创建Package的执行实例,以输出(output)参数返回执行实例的ID。

二,传递参数

向执行实例传递参数是可选的步骤,参数分为Package参数和系统参数,系统参数控制Packag的行为,如果参数@object_type的值为50,表示为执行实例设置系统参数。

--set package parameterdeclare @var0 sql_variant=N'Package_Parameter_value';exec catalog.set_execution_parameter_value     @Execution_ID=@Execution_ID,    @object_type=30,        --Package parameter    @parameter_name=N'Package_Parameter_Name',    @parameter_value=@var0--set system parameterdeclare @var1 smallint = 1exec [SSISDB].[catalog].[set_execution_parameter_value]     @execution_id=@execution_id,      @object_type=50,        --system parameter    @parameter_name=N'LOGGING_LEVEL',     @parameter_value=@var1exec [SSISDB].[catalog].[start_execution] @execution_id

使用存储过程 catalog.set_execution_parameter_value 来修改参数的执行值(Execution Value):

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

参数 @object_type的值是20、30 和50,分别表示工程级别的参数(Project Parameter),包级别的参数(Package Parameter),以及系统参数;

参数 @parameter_name 是参数的名称,对于系统参数,共有以下7个:

  • LOGGING_LEVEL
  • CUSTOMIZED_LOGGING_LEVEL
  • DUMP_ON_ERROR
  • DUMP_ON_EVENT
  • DUMP_EVENT_CODE
  • CALLER_INFO
  • SYNCHRONIZED

参数@parameter_value是参数的值,例如,设置@object_type=50,@parameter_name=‘SYNCHRONIZED’,@parameter_value为1,表示以同步方式启动执行实例。

三,开始执行实例

为执行实例传递相应的参数之后,可以调用SP运行Package的执行实例。在实例的脚本中,由于没有启用同步模式(设置系统参数:SYNCHRONIZED为1),因此,该SP以异步方式执行Package,不等Package执行完成,该SP就立即返回。

exec catalog.start_execution @Execution_ID=@Execution_IDgo

四,执行实例的运行模式

SSIS 引擎默认以异步方式执行Package,这意味着,在调用SP:catalog.start_execution 开始执行实例之后,SP不等待package执行完成,立即返回,返回的结果值表示调用Package的结果。如果后续执行的Package必须在前一个Package执行完成之后才能开始运行,那么,必须以同步方式执行Package,所谓同步模式,就是说,在调用sp:catalog.start_execution 之后,SP不会立即返回,而是等package执行完成之后,SP才会返回,将控制权传递到下一条TSQL 语句。

参数 SYNCHRONIZED 控制Package的执行模式,是以同步,还是以异步方式执行,该参数的默认值是0,也就是说,禁用同步模式,通过把参数:SYNCHRONIZED 的执行值(Execution Value)修改为1,使SSIS Engine以同步方式执行Package:

exec catalog.set_execution_parameter_value                 @execution_id=@package_execution_instance_id,                 @object_type= 50,                 @parameter_name = N'SYNCHRONIZED',                 @parameter_value = 1;

SYNCHRONIZED是一个系统参数,参数@object_type必须设置为50,当设置参数SYNCHRONIZED的执行值(Execution Value)为1,存储过程:catalog.start_execution 等到Package执行完成之后返回。

参考文档:

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

catalog.create_execution (SSISDB Database)

catalog.start_execution (SSISDB Database)

catalog.set_execution_parameter_value (SSISDB Database)

catalog.executions (SSISDB Database)

第七城市

微信扫一扫

第七城市微信公众平台