SSISDB7: View the currently running package

In the project team to do ETL development, often asked: "Now ETL is running which package?" ”

In order to shorten the ETL running time, in the ETL design, often uses the concurrent execution pattern: The Task executes concurrently, the package executes concurrently. For the package concurrency execution pattern, the implementation is implemented by deploying the package job on SSIS server, each JOB step executing a package Manager that calls execute in a concurrent manner Package Task, so that at the same time there are multiple child packages running concurrently, and the maximum number of concurrent executions of executable is the amount of CPU. How do I see the package that is running during the job run? As any package executes, SSIS records the historical messages produced by its executable (task,container) execution process, so that the operation message and executable can be recorded through the package. Name to determine which package is currently running.

SSIS engine uses the catalog.operation_messages view of Ssisdb to record the historical messages that each package produces during execution, using catalog.operations records the operation of the package, primarily the deployment of project, the package execution, and the cleanup of the history.

1. View the running operation

Any action performed by the package in the integration Service catalogs is recorded in the Catalog.operations view, which is a key columns of the view:

• operation_type: operation_type=200 means 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 (All), package (Environment), (+),or instance of Executio N ().

The script to view the currently running operation is

Select Top  Oneop.operation_id, OPT.OPERATION_TYPE_DESCR, Op.created_time, OBT.OBJECT_TYPE_DESCR asobject_affected, op.object_id, op.object_name, OPS.OPERATION_STATUS_DESCR asstatus, Op.start_time, Op.end_time, Op.caller_name fromCatalog.operations op with(NOLOCK)Inner JoinHelper. Operationtype opt with(NOLOCK) onOp.operation_type=Opt.operation_typeInner JoinHelper. ObjectType OBT with(NOLOCK) onOp.object_type=Obt.object_typeInner JoinHelper. Operationstatus Ops with(NOLOCK) onOp.status=Ops.operation_statuswhereOp.operation_type= $  --Create_execution and Start_execution ($) andOp.object_type= -        --Project (+) andOp.statusinch(2,5)--running (2), pending (5)Order  byOp.created_timedesc

2. View Operation Message for SSIS Engine Records

SSIS engine is the event type that records operation message based on the trigger event (event), from which MESSAGE_TYPE_DESCR can view messages, from Message_source_ The task type that triggers the event can be seen in descr: Control flow tasks or Data Flow task.

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.

Select Top 111om.message, Om.message_time, MT.MESSAGE_TYPE_DESCR, MST.MESSAGE_SOURCE_DESCR fromCatalog.operation_messages om with(NOLOCK)Inner JoinHelper. MessageType MT with(NOLOCK) onOm.message_type=Mt.message_typeInner JoinHelper. Messagesourcetype MST with(NOLOCK) onOm.message_source_type=Mst.message_source_typewhereom.operation_id=104627Order  byOm.message_timedesc

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, please refer to the Appendix of "Ssisdb6:operation"

Reference doc:

Catalog.operation_messages (SSISDB Database)

Catalog.operations (SSISDB Database)

SSISDB7: View the currently running package