SSIS Execution Log Query on SQL SERVER 2016

; with cte_msg_type as (

select message_type, message_desc

from (

values

(-1,'Unknown')

, (120,'Error')

, (110,'Warning')

, (70,'Information')

, (10,'Pre-validate')

, (20,'Post-validate')

, (30,'Pre-execute')

, (40,'Post-execute')

, (60,'Progress')

, (50,'StatusChange')

, (100,'QueryCancel')

, (130,'TaskFailed')

, (90,'Diagnostic')

, (200,'Custom')

, (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages. The value of the message column for DiagnosticEx is XML text.')

, (400,'NonDiagnostic')

, (80,'VariableValueChanged')

) as a (message_type, message_desc)

), cte_msg_src_type as (

select message_source_type, message_source_desc

from (

values

(10,'Entry APIs, such as T-SQL and CLR Stored procedures')

,(20,'External process used to run package (ISServerExec.exe)')

,(30,'Package-level objects')

,(40,'Control Flow tasks')

,(50,'Control Flow containers')

,(60,'Data Flow task')

) a (message_source_type, message_source_desc)

)

select

O.object_name as FailedPackageName

, O.object_id

, O.caller_name

, O.server_name

, O.operation_id

, OM.message_time

, MT.message_desc

, MS.message_source_desc

, OM.message

from SSISDB.catalog.operation_messages AS OM

inner join SSISDB.catalog.operations AS O

on O.operation_id = OM.operation_id

inner join cte_msg_type MT

on MT.message_type = OM.message_type

inner join cte_msg_src_type MS

on OM.message_source_type = MS.message_source_type

where 1=1

and MT.message_type in (120, 130)

and OM.message_time >= convert(varchar(10), getdate()-10, 112)