SSIS package performanace and results and run time history

Question

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

Answer 1

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  

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