在执行Package时，SSISDB都会创建唯一的OperationID 和 ExecutionID，标识对package执行的操作和执行实例（Execution Instance），并记录operation message，统计executable的执行时间，便于developers 优化package的设计，对package进行故障排除。
一，在package发生错误时，查看失败的Executable
An executable is a task or container that you add to the control flow of a package.
select e.project_name,
opt.operation_type_descr as Operation,
obt.object_type_descr as object_type,
e.object_id,
ops.operation_status_descr as operation_status,
et.package_name,
et.package_path as ExecutablePath,--relative path
--es.execution_path as ExecutableFullPath,
et.executable_name,
cast(es.execution_duration/1000/60.0 as decimal(10,1))as Duration_M,
er.execution_result_descr as execution_result,
es.start_time
--,es.end_time,
from catalog.executions e
inner join helper.OperationType opt
on e.operation_type=opt.operation_type
inner join helper.ObjectType obt
on e.object_type=obt.object_type
inner join helper.OperationStatus ops
on e.status=ops.operation_status
inner join catalog.executables et
on e.execution_id=et.execution_id
inner join catalog.executable_statistics es
on et.executable_id=es.executable_id and et.execution_id=es.execution_id
inner join helper.ExecutionResult er
on es.execution_result=er.execution_result
where e.execution_id=104627 --Specified ExecutionID
--and es.execution_result=1 -- 1 (Failure)
--and et.package_name=N'PackageName.dtsx'
order by et.package_name,es.start_time
二，查看Operation记录的message
1, SSIS 记录海量的Operation Message 和Event Message 数据，在查看这些文本信息时，应该设置好查询条件。
select
opt.operation_type_descr as Operation,
obt.object_type_descr as object_type,
o.object_name,
ops.operation_status_descr as OperationStatus,
mt.message_type_descr as message_type,
mst.message_source_descr,
om.message,
om.message_time
from catalog.operations o
inner join helper.OperationType opt
on o.operation_type=opt.operation_type
inner join helper.ObjectType obt
on o.object_type=obt.object_type
inner join helper.OperationStatus ops
on o.status=ops.operation_status
inner join catalog.operation_messages om
on o.operation_id=om.operation_id
inner join helper.MessageType mt
on om.message_type=mt.message_type
inner join helper.MessageSourceType mst
on om.message_source_type=mst.message_source_type
where o.operation_id =104627
and om.message_type in
(
120,--Error
110,--Warning
130--TaskFailed
)
order by om.message_time desc
2，查看Operation 的Event message，对Package进行troubleshoot时，Event Message非常有用
select
opt.operation_type_descr as Operation,
obt.object_type_descr as object_type,
o.object_name,
ops.operation_status_descr as OperationStatus,
em.event_message_id,
em.package_name,
em.event_name,
em.message_source_name,
em.subcomponent_name,
mt.message_type_descr as message_type,
mst.message_source_descr as message_source_type,
em.package_path,
em.event_message_id,
em.message_time,
em.message
from catalog.operations o
inner join helper.OperationType opt
on o.operation_type=opt.operation_type
inner join helper.OperationStatus ops
on o.status=ops.operation_status
inner join helper.ObjectType obt
on o.object_type=obt.object_type
inner join catalog.event_messages em
on o.operation_id=em.operation_id
inner join helper.MessageType mt
on em.message_type=mt.message_type
inner join helper.MessageSourceType mst
on em.message_source_type=mst.message_source_type
where o.operation_id =104627
and em.message_type in
(
120, --Error
110, --Warning
130 --TaskFailed;
)
--and em.package_name=N'PackageName.dtsx'
order by em.message_time desc
3，查看Event Message 的Context，以及相应的Property 和PropertyValue，这是最底层的SSIS 执行时Event 记录的值，能够查看到Package执行时的连接字符串的值
select emc.context_depth,
emc.package_path,
ct.context_type_name as context_type,
emc.context_source_name,
emc.property_name,
emc.property_value
from catalog.event_message_context emc
inner join helper.ContextType ct
on emc.context_type=ct.context_type
where emc.event_message_id=23929777
and emc.context_type=70
Appendix：
关于辅助表，请参考《SSISDB6：Operation》的附件
参考文档：