Querying metadata in SSIS for specific task durations

dbodell

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?

Jon_Shaulis

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;

dbodell

You rock!!!