WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, November 12, 2013

Getting run time for "child" SSIS packages out of SSISDB

Today I was looking at an SSIS package, it's sole job is to call a bunch of other SSIS packages in a specific order (loading a bunch of dimension tables).  Given some recent data growth, I was looking for low hanging fruit to help shorten our load times.  Interestingly enough, SSISDB, nor the Integration Services Catalog, have an obvious way to view these "inner" SSIS package statistics via the normal channels.  My Google fu was not working after several tries so I whipped this up.  Hopefully I hit all the keywords for the next person trying to find something similar.

I am extrapolating the "duration" from the first message to the last, so keep that in the back of your mind.  It gave me what I needed which was a place to focus based on run time.

 SELECT exe.folder_name, exe.project_name, exe.package_name as master_package,details.*  
 FROM ssisdb.catalog.executions exe  
     INNER JOIN (  
                 SELECT op.operation_id,created_time,object_name,evt.package_name,DATEDIFF(MINUTE,stDate,EndDate) as MinDur  
                 FROM SSISDB.[internal].[operations] op  
                     INNER JOIN (  
                                 SELECT package_name,operation_id,MIN(message_time) as stDate, MAX(message_time) AS endDate  
                                 FROM ssisdb.catalog.event_messages  
                                 GROUP BY package_name,operation_id  
                                 ) evt on op.operation_id = evt.operation_id  
                 ) details ON exe.execution_id = details.operation_id  
 WHERE  exe.project_name = 'SSISProjectName'   
     AND exe.start_time > '2013-11-05'   
     AND exe.package_name = 'TheSSISPackageParent.dtsx'  
 ORDER BY start_time