none
How to get package tasks execution history from SSISDB?

    Question

  • 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.

    Thanks,
    Eric

    --Package history query.
    SELECT 
    	  project_name
    	, 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
    ORDER BY 
    	  package_name
    	, start_time DESC
    	, end_time

    Saturday, January 12, 2019 12:07 AM

Answers

  • 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 
     ORDER BY 1 DESC


    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 MSDNFSF@microsoft.com

    • Proposed as answer by Yang.Z Wednesday, January 16, 2019 8:36 AM
    • Marked as answer by SQL Server dude Thursday, January 24, 2019 10:11 PM
    Monday, January 14, 2019 2:08 AM

All replies

  • The [catalog].[event_messages] view may have the information you are looking for.
    • Proposed as answer by Yang.Z Wednesday, January 16, 2019 8:35 AM
    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, http://sqlblog.com/blogs/uri_dimant/

    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 
     ORDER BY 1 DESC


    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 MSDNFSF@microsoft.com

    • Proposed as answer by Yang.Z Wednesday, January 16, 2019 8:36 AM
    • Marked as answer by SQL Server dude Thursday, January 24, 2019 10:11 PM
    Monday, January 14, 2019 2:08 AM