Data World

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

Posted by Pramod Singla on November 27, 2015


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

Advertisements

One Response to “SSIS 2012 (SSISDB)- Check execution status of SSIS Package”

  1. […] How to check the execution status of  a SSIS package,deployed in SSISDB, using system tables.Ans […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: