none
Task timing RRS feed

  • Question

  • I have run the query, below, to get the timing of tasks within a package.  All tasks show, roughly, the same start time as that of the package.  I am guessing all these tasks, although they depend on other tasks, start at the same time but blocked by dependency.  Is there a way to get the actual execution time of a task?  Thanks in advance

    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
     where package_name='Process.dtsx'
     GROUP BY event_messages.operation_id ,  package_name ,  message_source_name 
     ORDER BY 1 DESC

     
    Monday, October 30, 2017 10:30 PM

Answers

  •   SELECT  operation_id , package_name    , message_source_name , Task_Start , Task_Finish,  DATEDIFF(SECOND,Task_Start, Task_Finish  ) [time_Take_Seconds] ,  IsTaskFinished  =CASE WHEN Task_Finish IS NOT NULL THEN  'Finished' ELSE 'Not Finished' END 
      FROM 
      (
    	 SELECt operation_id , package_name    , message_source_name , 
    	 MIN(CASE WHEN message like '%Start,%' THEN message_time  END ) Task_Start ,  
    	 MAX(CASE WHEN message like '%Finished,%' THEN message_time  END )  Task_Finish  
    	 FROM SSISDB.[catalog].[event_messages]    
    	 WHERE     package_name='Process.dtsx'
    	 GROUP BY operation_id , package_name  , message_source_name 
      ) t 


    • Edited by msbi_Dev Tuesday, October 31, 2017 1:04 AM
    • Marked as answer by rgelfand Tuesday, October 31, 2017 4:28 PM
    Tuesday, October 31, 2017 1:03 AM

All replies

  •   SELECT  operation_id , package_name    , message_source_name , Task_Start , Task_Finish,  DATEDIFF(SECOND,Task_Start, Task_Finish  ) [time_Take_Seconds] ,  IsTaskFinished  =CASE WHEN Task_Finish IS NOT NULL THEN  'Finished' ELSE 'Not Finished' END 
      FROM 
      (
    	 SELECt operation_id , package_name    , message_source_name , 
    	 MIN(CASE WHEN message like '%Start,%' THEN message_time  END ) Task_Start ,  
    	 MAX(CASE WHEN message like '%Finished,%' THEN message_time  END )  Task_Finish  
    	 FROM SSISDB.[catalog].[event_messages]    
    	 WHERE     package_name='Process.dtsx'
    	 GROUP BY operation_id , package_name  , message_source_name 
      ) t 


    • Edited by msbi_Dev Tuesday, October 31, 2017 1:04 AM
    • Marked as answer by rgelfand Tuesday, October 31, 2017 4:28 PM
    Tuesday, October 31, 2017 1:03 AM
  • Why not to use the SSIS operational reports?

    Arthur

    MyBlog


    Twitter

    Tuesday, October 31, 2017 1:06 AM
    Moderator
  • Hi rgelfand,

    As far as I know, besides the operational report, there are no other exact logs to log the time for each Task executed in SSISDB internal tables or MSDB log tables.

    Generally, as a workaround, we can add a Execute SQL Task to write the start time into a table at the beginning of task executing and add another Execute SQL Task to write the end time into the table at the end of task executing. In this way, we can get the timing of tasks within a package.

    Check if this helps.

    Regards,

    Pirlo Zhang


    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 Visakh16MVP Tuesday, October 31, 2017 7:54 AM
    Tuesday, October 31, 2017 7:28 AM
    Moderator