Returns the minimum, maximum, average, deviation and total time spent executing data flow components.

DataFlowTimings.sql

-- Set this value to a reasonable value to filter out extraneous timings
DECLARE @MinTimingThresholdMilliseconds = 10;

SELECT
	[EventSource],
	[Component],
	COUNT([Timing]) [EventCount],
	MIN([Timing]) [Min Time (ms)],
	AVG([Timing]) [Average Time (ms)],
	MAX([Timing]) [Max Time (ms)],
	STDEVP([Timing]) [Time Variance (ms)],
	SUM([Timing]) / 1000.0 [Total Time (s)],
	100 / SQRT(COUNT([Timing])) [Sample Error %]
FROM
(
	SELECT
		[id] [EventID],
		[source] [EventSource],
		[starttime] [EventTime],
		SUBSTRING([message], 5, PATINDEX('%spent%', [message]) - 6) [Component],
		CAST(SUBSTRING([message], PATINDEX('%spent%', [message]) + 6, PATINDEX('%milliseconds%', [message]) - (PATINDEX('%spent%', [message]) + 6)) AS int) [Timing]
	FROM [dbo].[sysssislog]
	WHERE [event] = 'User:PipelineComponentTime'
) [Data]
WHERE [Timing] > @MinTimingThresholdMilliseconds
GROUP BY [EventSource], [Component]