Querying metadata in SSIS for specific task durations

dbodelldbodell Posts: 6 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

  • Jon_ShaulisJon_Shaulis Posts: 3 Bronze 1
    Accepted Answer
    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;
    SELECT TOP 100
           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;


Answers

Sign In or Register to comment.