Querying the SSIS Catalog? Here’s a handy query!
- by jamiet
I’ve been working on a SQL Server Integration Services (SSIS) solution for about 6 months now and I’ve learnt many many things that I intend to share on this blog just as soon as I get the time. Here’s a very short starter-for-ten… I’ve found the following query to be utterly invaluable when interrogating the SSIS Catalog to discover what is going on in my executions: SELECT event_message_id,MESSAGE,package_name,event_name,message_source_name,package_path,execution_path,message_type,message_source_typeFROM ( SELECT em.* FROM SSISDB.catalog.event_messages em WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions) AND event_name NOT LIKE '%Validate%' )q/* Put in whatever WHERE predicates you might like*/--WHERE event_name = 'OnError'--WHERE package_name = 'Package.dtsx'--WHERE execution_path LIKE '%<some executable>%'ORDER BY message_time DESC
Know it. Learn it. Love it.
@jamiet
