How to get package tasks execution history from SSISDB?


  • Hello,

    Question: How do I query SSISDB for the execution history of tasks within a specific package?

    Background: We need to improve the performance of some monstrous SSIS 2012 packages that take far too long to execute.  We'd like start by identifying the longest-running packages as well as some of the longest-running tasks within those packages.  I found the below query that returns exactly what we need for package history, but I'm having a hard time getting the history of the individual SSIS tasks.


    --Package history query.
    	, package_name
    	, [status]
    	, CASE [status] 
    		WHEN 1 THEN 'created' 
    		WHEN 2 THEN 'running' 
    		WHEN 3 THEN 'canceled' 
    		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 StatusDesc 
    	, start_time
    	, end_time
    	, DATEDIFF(minute, start_time, end_time) AS 'execution_time(min)'
    FROM [catalog].executions
    	, start_time DESC
    	, end_time

    Saturday, January 12, 2019 12:07 AM

All replies

  • The [catalog].[event_messages] view may have the information you are looking for.
    Saturday, January 12, 2019 7:38 PM
  • Go to SSIDB catalog and navigate to the package you want to see the  execution history

    Then right click on the package ---reports--- standard reports --- all executions 

    Best Regards,Uri Dimant SQL Server MVP,

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 13, 2019 2:28 PM
  • Hi 

    Please check the query post by msbi_Dev in this thread: 

    Execution time of each task of package from SSISDB catalog

     SELECT event_messages.operation_id ,  package_name , message_source_name  , 
     MIN( message_time  ) Task_Start,
     MAX( message_time  ) Task_Finish,
     DATEDIFF(SECOND, MIN( message_time  ) , MAX( message_time  )  ) [time_Take_Seconds]
     FROM    SSISDB.[catalog].[event_messages] 
     JOIN SSISDB.[catalog].[operations]  ON operations.operation_id = event_messages.operation_id
     GROUP BY event_messages.operation_id ,  package_name ,  message_source_name 

    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, January 14, 2019 2:08 AM