SSIS Catalog Useful Queries.

Some useful queries for troubleshooting and monitoring performance of SSIS Packages running in Project Deployment Model.

-- Get All executions Packages.

SELECT * FROM catalog.executions AS E

-- Get Currently Executing Package.

SELECT * FROM catalog.executions AS E

WHERE E.end_time IS NULL

-- Get Error Messages from latest execution.

 SELECT  *

       FROM    SSISDB.catalog.event_messages em

       WHERE   em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)

           AND event_name LIKE '%OnError%'

-- Get Exeuction duration for each object

select  *

from catalog.executable_statistics

where execution_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions) -- latest execution.

order by start_time