SSISDB5：使用TSQL指令碼執行Package

標籤：create   結果   runtime   book   etl   模式   賦值   sql   資料   

通常情況下，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                 @[email protected]_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)

SSISDB5：使用TSQL指令碼執行Package