SSISDB5：使用TSQL腳本執行Package

通常情況下，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 bigint
exec 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 parameter
declare @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 parameter
declare @var1 smallint = 1
exec [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id=@execution_id,  
    @object_type=50,        --system parameter
    @parameter_name=N'LOGGING_LEVEL', 
    @parameter_value=@var1
exec [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_ID
go

四，執行實例的運行模式

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)