SSISDB7: The currently running package and its executable

PM Q: "Vic, which package is now running ETL job, which task is being executed?" "The first time I encountered this problem, I was so confused that I could only bite the bullet and say," Let me see. "

This problem is common in project development, but is overlooked by many ETL development engineers, possibly because it is not a proposition that can be directly answered.

In large-scale data processing, ETL package development engineers often use the manager mode to design the package, that is, the manager package calls the sub-package, through the precedence constraints control the concurrent invocation and execution order of the sub-package, This mode is useful for managing a large number of package ETL projects. When packages is deployed to SQL Server using this mode, the developer only needs to create a job, set the schedule (Schedule), then the Big Butler (Agent) will automatically call the package, complete the data processing, peace of mind.

In addition to ease of deployment, the manager model can shorten the overall ETL run time. In the design of ETL package scheduling, the concurrent execution pattern is often used: Task concurrency execution, package concurrent execution. The concurrency execution pattern for the package is implemented by deploying the package job on SSIS server, with each Job step executing a manager package that calls execute in a concurrent manner Package Task, so that at the same time there are multiple child packages running concurrently, each of which is a executable file, and the maximum number of concurrent execution executable is the amount of CPU.

Typically, the job activity Monitor is used to view job execution, but only the history of the execution of a single job is seen, and when the package runs abnormally, the developer simply passes the monitor, The package and other lower-level messages that are currently running are not visible. So how do you see the package that is running while the job is running? The answer is a message logged through SSISDB. In project deployment mode, when any package executes, the SSIS engine records the historical messages that executable (Task,container) produces during execution, so that the operation can be logged through SSIS Message and executable name to determine which package is currently running.

The SSIS execution engine uses SSISDB to store the history of the package execution, and the SSIS Engine abstracts the execution of the package into one operation (Opertion), and the operation type is primarily the deployment of project. Package execution and message cleanup (cleanup). Each execution of the Package,ssis execution engine creates a operation_type=200 operation, using catalog.operations to record operation for the package, using catalog.operation_messages view that records the historical messages generated by each package during execution, with the message describing the object executable, Each executable is an executable component in the package, primarily task and container, able to infer the currently executing executable by executable name, event name, and time of creation of the message. The currently executing package is inferred.

If someone had read my previous blog, You should remember the Catalog.executables view, but, from this view, you can only infer that the completion (Executed) of the executable has been performed, and cannot infer the executable that is being executed (executing), so there is no shortcut to the direct conclusion, then we will press the department On the class, dry, code more, the article is a bit dull, but also please mercy.

1. View the running operation

Any actions performed by the package in the integration Service catalogs are recorded in the Catalog.operations view, and the key fields of the view are:

• Operation_type: operation_type=200 represents create_execution and start_execution
• status: The status of the operation. The possible values are created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9).
• object_type: The type of object affected by the operation. The object may be a folder (Ten), Project (+), package (50), Environment (+), or instance of the execution (a).

Too lazy to translate, I believe everyone's English level, to see the currently running Pperation, you can set the query condition: operation_type=200,status=2 or 5,object_type= 20, each opertion has a unique identification ID, which is associated with the opertaion message, and the query script is:

Select top One     op.operation_id,    opt.operation_type_descr,    op.created_time,    OBT.OBJECT_TYPE_DESCR As object_affected,    op.object_id,    op.object_name,    ops.operation_status_descr as status,    Op.start_ Time,    op.end_time,    op.caller_namefrom catalog.operations op with (nolock) inner join helper. Operationtype opt with (nolock) on    op.operation_type=opt.operation_typeinner join helper. ObjectType obt with (nolock) on    op.object_type=obt.object_typeinner join helper. Operationstatus ops with (NOLOCK) on    op.status=ops.operation_statuswhere op.operation_type=200  --create_ Execution and Start_execution ($) and op.object_type=20        --Project (a) and op.status in (2,5)        --running (2), Pending (5) Order BY op.created_time Desc

2. View Operation Message for SSIS Engine Records

The SSIS Engine records operation message based on events (event) triggered by executable, from MESSAGE_TYPE_DESCR to view the event type of the message, from Message_source_ The task type that triggers the event can be seen in descr: Control flow tasks or Data Flow task.

With OPERATION_ID, associate operation message, view the executable name that is logged by the SSIS engine when the package executes, identify the executable that is currently executing, and then determine which package is being executed.

The MSDN description for Catalog.operation_messages is:

This view, displays a row for each message, is logged during an operation in the catalog. The message can be generated by the server, by the execution process, or by the execution engine.

The query scripts used to view the event and component names are:

Select Top 111    om.message,    om.message_time,    mt.message_type_descr,    mst.message_source_descrfrom Catalog.operation_messages om with (nolock) inner join helper. MessageType MT with (NOLOCK) on    om.message_type=mt.message_typeinner join helper. Messagesourcetype MST with (NOLOCK) on    om.message_source_type=mst.message_source_typewhere om.operation_id= 104627order by om.message_time Desc

The message field provides very detailed information in the form of the task component name + event name + other, which is inferred from the component name to the running package and component. If the name of a task component is representative, it is easy to infer that the running task is running in the package and packages.

3,helper Auxiliary Table

For helper tables, refer to "Appendix" in "Ssisdb6:operation"

Reference doc:

Catalog.operation_messages (SSISDB Database)

Catalog.operations (SSISDB Database)

SSISDB7: The currently running package and its executable