SSIS Catalog2:view Usage

SCRIPT1, to see if a package performs executable properties and executable execution results when an error occurs

SelectE.project_name, E.operation_type,--refer to [catalog]. [Operations]    --E.package_name as Fristexecutepackagename,e.object_type,e.object_id,e.status,Et.package_name,et.package_path asExecutablepath,et.executable_name,--Es.execution_path as Executablefullpath,Es.execution_duration asExecution_duration_ms, Es.execution_result,es.start_time--, Es.end_time, fromcatalog.executions eInner JoinCatalog.executables et one.execution_id=et.execution_idInner Joincatalog.executable_statistics es onet.executable_id=es.executable_id andet.execution_id=es.execution_idwheree.execution_id=103600        --Specified Executionid    --and es.execution_result=1     andEt.package_name=N'packagename.dtsx'Order  byEt.package_name,es.start_time

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

SCRIPT2, view the message for the Operation record

--Operationtype=200:create_execution and Start_execution--operationstatus:created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6),--succeeded (7), stopping (8), and completed (9)--messagetype:120 Error, Warning, taskfailed--Message Source Type--Ten Entry APIs--External process used to run package--Package-level Objects--Control Flow Tasks--Control Flow Containers--Data Flow TaskSelectO.operation_type,o.object_name, O.status asOperationstatus,--O.start_time,o.end_time,Om.message_type,om.message_source_type,om.message,om.message_time fromcatalog.operations oInner Joincatalog.operation_messages om ono.operation_id=om.operation_idwhereo.operation_id=103600      andOm.message_typeinch    (         -,--Error         the,--worning         the--taskfailed    )Order  byOm.message_timedesc

SCRIPT3, view the operation related event message and record the event message corresponding to the event_message_id to see the event Context at the time it occurred.

--Operationtype=200:create_execution and Start_execution--operationstatus:created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6),--succeeded (7), stopping (8), and completed (9)--messagetype:120 Error, Warning, taskfailed--Message Source Type--Ten Entry APIs--External process used to run package--Package-level Objects--Control Flow Tasks--Control Flow Containers--Data Flow TaskSelectO.operation_type,o.object_name, O.status asOperationstatus, Em.package_name,em.event_name,em.message_source_name,em.subcomponent_name, Em.message_type, Em.message_source_type,em.package_path,em.event_message_id,em.message_time fromcatalog.operations oInner Joincatalog.event_messages em ono.operation_id=em.operation_idwhereo.operation_id=103600      andEm.message_typeinch    (         -,--Error         the,--worning         the     --taskfailed;    )     andEm.package_name=N'packagename.dtsx'Order  byEm.message_timeASC

Script4, 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, facilitating troubleshoot.

-- ContextType:    ten Tasks, Sequence, + Foreach Loop, package, Variable, Connection Manager,--                Pipeline (source, destination, or transformation component)Select  emc.context_depth, Emc.package_path,emc.context_type,emc.context_source_name,emc.property_name,emc.property_valuefrom  catalog.event_message_context EMCwhere emc.event_message_id=23929777   and Emc.context_type=

Reference Documentation:

https://msdn.microsoft.com/en-us/library/ff878135 (v=sql.110). aspx

https://msdn.microsoft.com/en-us/library/hh479588 (v=sql.110). aspx

SSIS Catalog2:view Usage