Getting ETL Task Duration
Happy New Year!
ETL exceeds the processing time window? Optimizing ETL, starts with obtaining task-level execution times? If you use SSIS 2012 project deployment mode, task-level stats are already loaded in the SSIS catalog and you can use the following query:
SELECT execution_id,
CASE WHEN [status] = 1 THEN ‘created’
WHEN [status] = 2 THEN ‘running’
WHEN [status] = 3 THEN ‘canceled’
WHEN [status] = 4 THEN ‘failed’
WHEN [status] = 5 THEN ‘pending’
WHEN [status] = 6 THEN ‘ended unexpectedly’
WHEN [status] = 7 THEN ‘succeeded’
WHEN [status] = 8 THEN ‘stopping’
WHEN [status] = 9 THEN ‘completed’
END AS [status_text],
DATEDIFF(ss,start_time,end_time) DurationInSeconds
FROM catalog.executions e
What if you are not on SSIS 2012 or later yet or you are not using the project deployment mode or a framework that logs the task duration? You can still obtain the task duration but you need to enable SSIS logging for each package you want to monitor, as follows:
- Open the package in BIDS/SSDT.
- On the SSIS menu, click Logging. Configure logging to use the SSIS Log Provider for SQL Server. The provider will save the statistics in a SQL Server table so you can easily query the results.
- On the Details tab, select the OnPreExecute, OnPostExecute, and most importantly the OnProgress event so you can get the same level of execution statistics as in the BIDS/SSDT Progress tab.
- Once you configure the SSIS Log Provider for SQL Server, it will create a sysssislog table in the database you specified when you configured the provider. When the SQL Server Agent executes your package, you can use a query like the one below to obtain task-level durations:
SELECT executionid Execution,
PackageName = ( SELECT TOP 1 source FROM dbo.sysssislog S WHERE S.executionid = L.executionid AND S.[event] = ‘PackageStart’ ),
PackageStep = l.source,
Runtime_min = DateDiff(minute, MIN(L.starttime), MAX(L.endtime)),
Runtime_hr = DateDiff(hour, MIN(L.starttime), MAX(L.endtime)),
StartTime = MIN(L.starttime),
EndTime = MAX(L.endtime),
FinishDate = CAST(MAX(L.endtime)AS DATE)
FROM dbo.sysssislog L WHERE L.[event] != ‘PackageStart’
GROUP BY executionid, L.source
