If you’ve ever had experience of working with the Integration service catalog in SQL Server 2012 and above, you’ll know when it comes down to running one of the execution reports it can be painfully slow (Dependent on the amount of execution history you retain, see one of my previous posts about that), this despite them being quite informative.
Therefore I thought I’d share a SQL query which I generally use on a daily basis to either find errors/bugs which have occurred during an SSIS package execution or see how certain areas of a package are performing during execution.
I can’t take full credit for the original SQL creation, that was the handy work of Jamie Thompson a SSIS guru, however I have extended the query (very slightly) adding in some additions which I’ve found useful.
SELECT event_message_id ,[Message] ,package_name ,event_name ,message_source_name ,package_path ,execution_path ,message_type ,message_source_type FROM ( SELECT em.* FROM SSISDB.CATALOG.event_messages em(NOLOCK) WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions) --Find currently executing details --WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.CATALOG.executions WHERE folder_name = 'SSIS Catalog folder name') --Find last execution at folder level --WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.CATALOG.executions WHERE folder_name = 'SSIS Catalog folder name' AND project_name = 'Project name') --Find last execution at project level ) q /* Put in whatever WHERE predicates you might like*/ --WHERE event_name NOT LIKE '%Validate%' --WHERE event_name = 'OnError' --WHERE package_name = 'packagename.dtsx' --WHERE execution_path LIKE '%<some executable>%' ORDER BY message_time DESC
I hope someone out there finds this useful 🙂