时间:2021-07-01 10:21:17 帮助过:21人阅读
如果有人看过我之前的博客,应该记得catalog.executables视图,但是,从该视图中,只能推断出已经执行完成(Executed)的Executable,而不能推断出正在执行(Executing)的Executable,所以,没有捷径直接得出结论,那我们就按部就班。
1,查看正在运行的operation
Integration Service Catalogs中Package执行的任何操作,都会记录在 catalog.operations 视图中,该视图的关键字段是:
懒得翻译了,相信大家的英语水平,要查看当前正在运行的pperation,可以设置查询条件:operation_type=200,status=2或5,object_type=20,每个opertion都有一个唯一的标识ID,通过该ID和opertaion message关联,查询脚本是:
select top 11 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_name from catalog.operations op with(nolock) inner join helper.OperationType opt with(nolock) on op.operation_type=opt.operation_type inner join helper.ObjectType obt with(nolock) on op.object_type=obt.object_type inner join helper.OperationStatus ops with(nolock) on op.status=ops.operation_status where op.operation_type=200 --create_execution and start_execution(200) and op.object_type=20 -- project (20) and op.status in(2,5) -- running (2), pending (5) order by op.created_time desc
2,查看SSIS Engine 记录的Operation Message
SSIS 引擎是根据Executable触发的事件(Event)来记录Operation Message的,从message_type_descr能够查看消息的 Event 类型,从message_source_descr中能够看到触发事件的Task 类型:Control Flow tasks 或 Data Flow task。
通过operation_id,关联operation message,查看在package执行时,SSIS引擎记录的Executable名字,确定当前正在执行的Executable,进而确定正在执行的Package。
MSDN对 catalog.operation_messages 的描述是:
This view displays a row for each message that is logged during an operation in the catalog. The message can be generated by the server, by the package execution process, or by the execution engine.
用来查看事件和组件名称的查询脚本是:
select top 111 om.message, om.message_time, mt.message_type_descr, mst.message_source_descr from catalog.operation_messages om with(nolock) inner join helper.MessageType mt with(nolock) on om.message_type=mt.message_type inner join helper.MessageSourceType mst with(nolock) on om.message_source_type=mst.message_source_type where om.operation_id=104627 order by om.message_time desc
Message字段提供的信息非常详细,格式大概是:Task组件名称+事件名称+其他,通过组件名称,推测正在运行的Package和组件。如果Task 组件的名称具有代表性,就能很容易推断出正在运行的Package 和 Package中正在运行的task。
3,helper 辅助表
关于helper 辅助表,请参考《SSISDB6:Operation》的“Appendix”
参考doc:
catalog.operation_messages (SSISDB Database)
catalog.operations (SSISDB Database)
SSISDB7:当前正在运行的Package及其Executable
标签:str each style job star schedule isp 运行 possible