SSISDB – Package Execution Time in Minutes and Average Execution Time

–By Lori Brown   @SQLSupahStah

I have a client who has a lot of SSIS packages in the SSIS catalog that we are migrating to a new server. Of course there is lots of testing going on. So the question came up about how to more easily see package runtimes and comparing to the overall average runtime rather than using the Dashboard or the reports that come with Management Studio. After a little searching, I found that the SSISDB.internal.execution_info table contains what I needed.

— Filter by date and package name

DECLARE @begindate DATE = GETDATE() – 7

DECLARE @pkgname VARCHAR(100) = ‘Your Package Name— Ex: Patient_Update.dtsx

 

— Package execution time

SELECT folder_name, project_name, package_name,

CAST(start_time AS datetime) AS start_time,

DATEDIFF(MINUTE, start_time, end_time) AS ‘execution_time[min]’

FROM SSISDB.internal.execution_info

WHERE start_time >= @begindate

AND package_name = @pkgname — Comment out this line for all packages

ORDER BY start_time

 

— Average package execution time

SELECT folder_name, project_name, package_name,

AVG(DATEDIFF(MINUTE, start_time, end_time)) AS ‘Avg execution_time[min]’

FROM SSISDB.internal.execution_info

WHERE start_time >= @begindate

AND package_name = @pkgname — Comment out this line for all packages

GROUP BY folder_name,project_name,package_name

You can also use the catalog.executions view to get the same data but since I saw the internal table, I jumped in. This was tested on SQL 2016 but I think it will still work in earlier versions. Hope this helps someone out!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

Both comments and trackbacks are currently closed.
%d bloggers like this: