SSISDB5: Executing the package with a TSQL script

Typically, an ETL engineer creates an agent job to periodically execute a stored procedure that package,agent the underlying call SSISDB (catalog.start_execution) to initiate an execution instance in synchronous mode, which means that We can simulate the behavior of the agent job and execute the package using the TSQL script. Each time the SSIS engine executes the package, an execution instance (execution Instance) is created and each execution instance has a unique identity executionid, and the data type is bigint.

An execution instance can contain parameters and use stored procedures (catalog.set_execution_parameter_value) to pass parameters to the execution instance. There are two types of parameters for executing instances: System parameters and Package parameters, 7 system parameters for controlling logging, Execution mode (asynchronous/synchronous), and package parameters for passing dynamic parameter values to the package. In order to control the behavior of the execution instance, the system parameters need to be passed to the execution instance; some package contain parameters and must be assigned before the instance is started.

One, create an instance of execution

The first step in executing the package is to create an execution instance of the package and save the ID of the execution instance. Use the catalog.create_execution stored procedure to create an execution instance that returns a unique instance ID (execution_id) through the output parameters after the creation is successful.

Declare @Execution_ID bigintexecCatalog.create_execution@package_name=N'test.dtsx',    @Execution_ID=@Execution_IDoutput,@folder_name=N'Myprojectfloder',    @project_name=N'MyProject',    @use32bitruntime=FALSE,@reference_id=NULL

The SP creates an execution instance of the package and returns the ID of the execution instance in the output parameter.

Second, pass the parameter

Passing parameters to the execution instance is an optional step, the parameters are divided into package parameters and system parameters, the system parameters control the behavior of the packag, and if the value of the parameter @object_type is 50, the system parameters are set for the execution instance.

--Set Package parameterDeclare @var0sql_variant=N'Package_parameter_value';execCatalog.set_execution_parameter_value@Execution_ID=@Execution_ID,    @object_type= -,--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= -,--system Parameter    @parameter_name=N'Logging_level',     @parameter_value=@var1exec [SSISDB].[Catalog].[start_execution] @execution_id

Use the stored procedure catalog.set_execution_parameter_value to modify the execution value of the parameter (execution value):

Catalog.set_execution_parameter_value        [  @execution_id = execution_id    ]  object_type    []  parameter_name    [] parameter_value

The values for the parameter @object_type are 20, 30, and 50, which represent the project-level parameters (Project Parameter), the package-level parameters (packages Parameter), and the system parameters;

The parameter @parameter_name is the name of the parameter, and for system parameters, there are 7 of the following:

• Logging_level
• Customized_logging_level
• Dump_on_error
• Dump_on_event
• Dump_event_code
• Caller_info
• SYNCHRONIZED

The parameter @parameter_value is the value of the parameter, for example, setting @object_type=50, @parameter_name = ' SYNCHRONIZED ', @parameter_value of 1, which indicates that the execution instance is started synchronously.

Three, start the execution instance

After you pass the appropriate parameters for the execution instance, you can call the SP to run the package execution instance. In the script for the instance, because synchronization mode is not enabled (set system parameters: Synchronized is 1), the SP executes the package asynchronously, and the SP returns immediately without waiting for the package execution to complete.

exec @Execution_ID = @Execution_ID Go

Four, the running mode of the execution instance

The SSIS engine executes the package asynchronously by default, which means that after calling SP:catalog.start_execution to start executing the instance, the SP does not wait for the package execution to complete, immediately returns, and the returned result value represents the result of calling the package. If the subsequent package must be executed before the previous package is completed, the package must be executed synchronously, the so-called synchronous mode, that is, after calling Sp:catalog.start_execution, The SP does not return immediately, but waits until the package execution is complete before the SP returns and passes control to the next TSQL statement.

The parameter SYNCHRONIZED controls the execution mode of the package, either synchronously or asynchronously, and the default value of this parameter is 0, that is, disable synchronous mode by putting the parameter:SYNCHRONIZED Execution Value (execution Value) is modified to 1 so that SSIS engine executes the package synchronously:

 exec   Catalog.set_execution_parameter_ Value  @[email protected]_execution_instance_id  ,   @object_type  =  50    @parameter_name  
    
     = 
     n SYNCHRONIZED   " ,   @parameter_value  =  
   
    1 ; 
   

Synchronized is a system parameter, the parameter @object_type must be set to 50 when the execution value of the set parameter synchronized (execution value) is 1, stored procedure: catalog.start_execution Wait until the package finishes executing.

Reference Documentation:

Quick Tip–run SSIS 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: Executing the package with a TSQL script