SSISDB8：查看SSISDB记录Package执行的消息

在执行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 einner join helper.OperationType opt on e.operation_type=opt.operation_typeinner join helper.ObjectType obt on e.object_type=obt.object_typeinner join helper.OperationStatus ops on e.status=ops.operation_statusinner join catalog.executables et on e.execution_id=et.execution_idinner join catalog.executable_statistics es on et.executable_id=es.executable_id and et.execution_id=es.execution_idinner join helper.ExecutionResult er on es.execution_result=er.execution_resultwhere 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_timefrom catalog.operations oinner join helper.OperationType opt on o.operation_type=opt.operation_typeinner join helper.ObjectType obt on o.object_type=obt.object_typeinner join helper.OperationStatus ops on o.status=ops.operation_statusinner join catalog.operation_messages om on o.operation_id=om.operation_idinner join helper.MessageType mt on om.message_type=mt.message_typeinner join helper.MessageSourceType mst on om.message_source_type=mst.message_source_typewhere 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.messagefrom catalog.operations oinner join helper.OperationType opt on o.operation_type=opt.operation_typeinner join helper.OperationStatus ops on o.status=ops.operation_statusinner join helper.ObjectType obt on o.object_type=obt.object_typeinner join catalog.event_messages em on o.operation_id=em.operation_idinner join helper.MessageType mt on em.message_type=mt.message_typeinner join helper.MessageSourceType mst on em.message_source_type=mst.message_source_typewhere 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_valuefrom catalog.event_message_context emcinner join helper.ContextType ct on emc.context_type=ct.context_typewhere emc.event_message_id=23929777and emc.context_type=70

Appendix：

关于辅助表，请参考《SSISDB6：Operation》的附件

参考文档：

Views (Integration Services Catalog)

SSIS Catalog

SSISDB8：查看SSISDB记录Package执行的消息

标签：tle   dev   tsx   evel   nta   查询条件   services   esc   log   

小编还为您整理了以下内容，可能对您也有帮助：

如何有效控制SSISDB的数据库文件的大小

1. 设置SSIDB Properties，仅保留1天的日志和3个project部署版本
SSISDB会创建一个agent job - SSIS Server Maintenance Job，根据以上配置来定期清理SSISDB内容。这个job的第一步会调用[SSISDB].[internal].[cleanup_server_retention_window]存储过程，涉及多表的级联删除，会产生大量transaction log。所以我们还需要进行以下操作。
2. SSISDB数据库的恢复模式默认是Full，如果不进行日志备份并截断，SSISDB.ldf会持续增长，很快占满磁盘。考虑到SSIDB并没有容灾要求，直接将恢复模式改为Simple Recovery，这样的好处是数据库Checkpoint之后会自动截断transaction log，SSISDB.ldf会控制在一个稳定的大小。但稳定值在40G左右还是无法接受，需要进一步优化
3. 在SSISDB中查看[internal].[cleanup_server_retention_window]的代码，在删除日志的核心代码中加入CHECKPOINT，促使数据库在每个级联删除事务之后尽快进行checkpoint，从而自动截断transaction log，下一轮循环中的删除操作就可以重用该文件空间；同时，减少@delete_batch_size为100，使每个批次中删除数据量减少。最终SSISDB.ldf稳定大小缩小至20G左右。
注意：需要给AllSchemaOwner赋予CHECKPOINT权限
4. 设置Customized Logging Level，只记录error和warning信息，这样就大大减少了写入SSIDB表中的记录数。DELETE时的事务日志自然也缩减了

如何有效控制SSISDB的数据库文件的大小

1. 设置SSIDB Properties，仅保留1天的日志和3个project部署版本
SSISDB会创建一个agent job - SSIS Server Maintenance Job，根据以上配置来定期清理SSISDB内容。这个job的第一步会调用[SSISDB].[internal].[cleanup_server_retention_window]存储过程，涉及多表的级联删除，会产生大量transaction log。所以我们还需要进行以下操作。
2. SSISDB数据库的恢复模式默认是Full，如果不进行日志备份并截断，SSISDB.ldf会持续增长，很快占满磁盘。考虑到SSIDB并没有容灾要求，直接将恢复模式改为Simple Recovery，这样的好处是数据库Checkpoint之后会自动截断transaction log，SSISDB.ldf会控制在一个稳定的大小。但稳定值在40G左右还是无法接受，需要进一步优化
3. 在SSISDB中查看[internal].[cleanup_server_retention_window]的代码，在删除日志的核心代码中加入CHECKPOINT，促使数据库在每个级联删除事务之后尽快进行checkpoint，从而自动截断transaction log，下一轮循环中的删除操作就可以重用该文件空间；同时，减少@delete_batch_size为100，使每个批次中删除数据量减少。最终SSISDB.ldf稳定大小缩小至20G左右。
注意：需要给AllSchemaOwner赋予CHECKPOINT权限
4. 设置Customized Logging Level，只记录error和warning信息，这样就大大减少了写入SSIDB表中的记录数。DELETE时的事务日志自然也缩减了