; 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)
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞0
添加新评论0 条评论