The SSISDB contains a wealth of statistical data for your SSIS package executions and messages. In a package failure scenario, trawling through SQL Management Studio to get detailed reports becomes quite tedious. For a very quick and easy dashboard looking at the most recent events try a simple Reporting Services report with two tables and two datasets. The two datasets are outlined in more detail below.
The first dataset takes the last 3 days worth of execution history for your packages as per the top table. Whilst this dataset returns one row per execution as displayed, adjusting the groupings in this query will give you interesting perspectives on Average Duration and Execution Count.
The second table contains the last 15 messages. In a failure scenario the second dataset will typically hold the most recent failure if no other packages have been run subsequent to the failure. However you may need to adjust the dataset to suit, where TOP is specified, or parameterise as required – add a little Dijon mustard 🙂
Execution Summary – Last 3 Days
SELECT folder_name, COUNT(*) AS tally_executions, SUM(DATEDIFF(ms, start_time, end_time))/1000 AS total_duration_s, package_name, CASE status WHEN 1 THEN 'Created' WHEN 2 THEN 'Running' WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'Failed' WHEN 5 THEN 'Pending' WHEN 6 THEN 'Ended Unexpectedly' WHEN 7 THEN 'Succeeded' WHEN 8 THEN 'Stopping' WHEN 9 THEN 'Completed' END AS Status, start_time FROM catalog.executions AS e WHERE start_time > getdate()-3 GROUP BY folder_name, package_name, status, start_time ORDER BY start_time DESC
Message Detail – Last 15 Messages
SELECT TOP (15) event_message_id, message, package_name, event_name, message_source_name FROM (SELECT event_message_id, operation_id, message_time, message_type, message_source_type, message, extended_info_id, package_name, event_name, message_source_name, message_source_id, subcomponent_name, package_path, execution_path, threadID, message_code FROM catalog.event_messages AS em WHERE (operation_id = (SELECT MAX(execution_id) AS Expr1 FROM catalog.executions)) AND (event_name NOT LIKE '%Validate%')) AS q ORDER BY message_time DESC
Here’s a couple of chilly reads that go a little deeper.