What are the challenges you face when working across database platforms? Take the survey

Querying metadata in SSIS for specific task durations

dbodelldbodell Posts: 7 New member
I am looking for a way to quickly analyze the durations over time of a particular task in an SSIS ETL that I have. I have used SSISDB All Executions report to see the duration of the task for one particular run by filtering on post execution messages and message source, but I would like to see the duration of that same task over the last two weeks and I can't see how to do that. I know there's a lot of SSIS metadata but the scripts I've seen posted show the durations of the packages, not specific tasks in the packages. Any help?

Best Answer

  • Options
    Jon_ShaulisJon_Shaulis Posts: 3 Bronze 1
    Greetings dbodell, I found a post located here that may have a solution for you. I'll post the code below in case the link ever expires. Let me know if this gets you close to what you want. I'm sure you can log or aggregate the data to a table and work off of that.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;<br>SELECT TOP 100<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; event_messages.operation_id,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; package_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message_source_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MIN(message_time) Task_Start,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MAX(message_time) Task_Finish,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DATEDIFF(SECOND, MIN(message_time), MAX(message_time)) [time_Take_Seconds]<br>FROM SSISDB.[catalog].[event_messages]<br>&nbsp;&nbsp;&nbsp; JOIN SSISDB.[catalog].[operations]<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON operations.operation_id = event_messages.operation_id<br>GROUP BY event_messages.operation_id,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; package_name,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message_source_name<br>ORDER BY 1 DESC;


Sign In or Register to comment.