SSIS 2012 (SSISDB)- Check execution status of SSIS Package

Problem Statement: How to check the execution status of  a SSIS package, deployed in SSISDB, using system tables?

Solution:

DECLARE @status INT=2/*Running The possible values are created (1), running 

(2), cancelled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), 

stopping (8), and completed (9)*/
SELECT *
FROM   catalog.executions
WHERE  status = @status

SELECT e.executable_id,e.package_name,
e.[executable_name],
es.[execution_path],
es.[execution_duration],
[execution_result] = CASE es.[execution_result]
WHEN 0 THEN ‘Success’
WHEN 1 THEN ‘Failure’
WHEN 2 THEN ‘Completion’
WHEN 3 THEN ‘Cancelled’
END,
es.start_time,
es.end_time
FROM   [SSISDB].[catalog].[executables] e
INNER JOIN [SSISDB].[catalog].[executable_statistics] es
ON es.[executable_id] = e.[executable_id]
WHERE  e.execution_id IN (SELECT execution_id
FROM   catalog.executions
WHERE  status = @status)
ORDER  BY e.executable_id DESC,es.start_time DESC

One comment

Leave a Reply

Your email address will not be published.