SSISDB8: View SSISDB record package execution messages

When the package is executed, Ssisdb creates unique OperationID and Executionid, identifies the operations and execution instances (execution Instance) performed on the package, and records the operation message, Statistical executable execution time, easy to developers optimize the package design, troubleshooting the package.

One, view the failed executable when the package error occurs

An executable are a task or container that's 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 Deci Mal (10,1)) as Duration_m, er.execution_result_descr as Execution_result, Es.start_time--, es.end_time,from Catalo G.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 joins Catalog.executables et on e.execution_id=et.executio N_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.pack Age_name=n ' packagename.dtsx ' ORDER by Et.package_name,es.start_time

Second, view the message of the Operation record

1, SSIS records a huge amount of operation message and event message data, and when viewing the text information, the query criteria should be set.

 Select Opt.operation_type_descr as operation, OBT.OBJECT_TYPE_DESCR as Object_type, O.object_name, OPS.O PERATION_STATUS_DESCR as Operationstatus, mt.message_type_descr as Message_type, MST.MESSAGE_SOURCE_DESCR, Om.mes Sage, 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 joins Catalog.operation_messages om on O.operation_id=om.op Eration_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 (,--Error,--Warning 130--taskfailed) Order BY om.message_time Desc 

2, view the event message for operation, which is useful when troubleshoot the package

Select Opt.operation_type_descr as operation, OBT.OBJECT_TYPE_DESCR as Object_type, O.object_name, Ops.operati ON_STATUS_DESCR as Operationstatus, em.event_message_id, Em.package_name, Em.event_name, Em.message_source_nam    E, 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 joins 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,--warning---taskfailed; )--and em.package_name=n ' packagename.dtsx ' ORDER by em.message_time Desc

3, view the context of the event Message, and the corresponding property and PropertyValue, which is the value of the event record at the lowest level of SSIS execution, and is able to view the value of the connection string when the package executes

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:

For auxiliary forms, please refer to the annex of the Ssisdb6:operation

Reference Documentation:

Views (integration Services Catalog)

SSIS Catalog

SSISDB8: View SSISDB record package execution messages