SSIS - Log Execution time
This query helps you to have a better view of your SSIS Packages executions.
When you run a “master job / package”, you don’t clearly see the execution time for your child packages in SSISDB Reports.
With this query, now it’s possible :)
USE SSISDB
GO
SELECT
package_name
,execution_id
,executable_id
,StartTime
,EndTime
,LastStatus
,execution_result
,RowIndex
,DATEDIFF(minute, StartTime, EndTime) AS 'execution_time[min]'
FROM
(
SELECT DISTINCT
executables.package_name AS package_name
,executables.execution_id AS execution_id
,executables.executable_id AS executable_id
,FIRST_VALUE(start_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY start_time ASC) AS StartTime
,FIRST_VALUE(end_time) OVER(PARTITION BY executables.package_name, executable_statistics.execution_id ORDER BY end_time DESC) AS EndTime
,FIRST_VALUE(execution_result) OVER(PARTITION BY executables.package_name ORDER BY end_time DESC) AS LastStatus
,execution_result AS execution_result
,ROW_NUMBER() OVER(partition by executables.package_name ORDER BY end_time DESC) AS RowIndex
FROM [catalog].[executables]
LEFT JOIN [catalog].[executable_statistics] ON
executable_statistics.execution_id = executables.execution_id
AND executable_statistics.executable_id = executables.executable_id
--WHERE executables.package_name = 'YourPackage.dtsx'
) A
WHERE a.RowIndex = 1
ORDER BY StartTime ASC</pre>