SSIS package performanace and results and run time history

kkran 581 Reputation points
2021-04-27T22:26:58.727+00:00

Hi Team - My SQL Server version is 2018 and VS 2017.

I have an SSIS Package that was deployed to the SSISDB folder under a project. The package has 20 child packages which execute them in sequence.
The job was scheduled to run the package every 1 hour but the package runs for almost 12 hours.

Is there any way I can query the history of the package. I want to know how long each child package is running from beginning to end for each package. I want to get these details for all the 20 child packages.

I can the Integration services catalog but how do I generate this through the database table? Thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
1,857 questions
No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 18,896 Reputation points Microsoft Employee
    2021-04-28T06:35:40.477+00:00

    Hi @kkran ,

    You may try with below query.

     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  
    

    91945-microsoftteams-image.png

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October


0 additional answers

Sort by: Most helpful