This code was written while we were migrating a bunch of SSIS packages to a new SQL Server version. It will show you the latest results for each package run. If a package ended in an error state, it will show the latest error that was generated.
You can toggle the code to show only packages in error state, and you can toggle the running packages on/off. Running packages can be included because otherwise, running packages do not show up in this list.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
DECLARE @ShowOnlyErrors BIT = 0, -- Show errors or also completed packages @ShowRunning BIT = 1 -- Show packages that are currently running to track progress ; WITH LatestExecution AS ( SELECT exe.folder_name, exe.project_name, exe.package_name, exe.process_id, opr.status, opr.operation_id, opr.start_time, opr.end_time, sort_order = ROW_NUMBER() OVER (PARTITION BY exe.folder_name, exe.project_name, exe.package_name ORDER BY exe.start_time DESC, opr.operation_id desc ) FROM SSISDB.CATALOG.OPERATIONS AS opr INNER JOIN SSISDB.CATALOG.executions AS exe ON opr.process_id = exe.process_id AND opr.object_id = exe.object_id AND opr.STATUS IN (2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded WHERE opr.object_type = 20 -- package AND opr.operation_type = 200 -- Package start AND ( opr.status <> 2 OR @ShowRunning = 1) ), LatestMessage AS ( SELECT msg.operation_id, msg.message, msg.execution_path, msg.package_path, msg.message_time, msg.message_type, lex.start_time, lex.end_time, lex.process_id, lex.status, lex.folder_name, lex.project_name, lex.package_name, sort_order = ROW_NUMBER() OVER (PARTITION BY msg.operation_id ORDER BY msg.message_type DESC, msg.message_time DESC ) FROM SSISDB.catalog.event_messages AS msg INNER JOIN LatestExecution AS lex ON lex.operation_id = msg.operation_id AND lex.status IN (2, 4, 5, 6, 7) -- 2 running, failed, pending, ended unexpectedly, succeeded AND lex.sort_order = 1 WHERE msg.message_type = 120 OR ( msg.message_type IN (30, 40) AND msg.package_path = '\package' AND msg.event_name = 'OnPostExecute' AND @ShowOnlyErrors = 0 ) ) SELECT DISTINCT lms.folder_name, lms.project_name, lms.package_name, status = IIF(lms.status = 2, 'RUNNING', IIF(lms.message_type = 120, 'ERROR', 'SUCCESS')), lms.message, lms.operation_id, lms.execution_path, lms.package_path, lms.start_time, lms.message_time, runtime = CAST(DATEDIFF(DAY, lms.message_time , lms.start_time) AS VARCHAR(5)) + ' ' + FORMAT(CAST(lms.message_time AS DATETIME) - CAST(lms.start_time AS DATETIME), 'HH:mm:ss') FROM LatestMessage AS lms WHERE lms.sort_order = 1 ORDER BY lms.start_time DESC, lms.message_time; |