Identifying Bottlenecks in SSIS Packages

USE SSISDB

/* Change package name as required */
DECLARE @PackageName NVARCHAR(520) = 'Package.dtsx',
    @ExecutionId BIGINT;
 
/* Select which execution, customise as needed */
SELECT TOP (1) @ExecutionId = e.execution_id
FROM internal.executions e
WHERE e.package_name = @PackageName
ORDER BY e.execution_id DESC;

/* Return the summary */
SELECT ExecutionId = es.execution_id,
    TaskPath = es.execution_path,
    StartDateTime = CAST(es.start_time AS DATETIME2(0)),
    EndDateTime = CAST(es.end_time AS DATETIME2(0)),
    RunTimeSeconds = es.execution_duration / 1000,
    [%Total] = CAST(100 * es.execution_duration / SUM(CAST(es.execution_duration AS FLOAT)) OVER () AS DECIMAL(5,2)),
    Indicator = REPLICATE('+', es.execution_duration / (SUM(CAST(es.execution_duration AS FLOAT)) OVER () * 0.05)),
    w.Warnings,
    rw.RowsWritten
FROM internal.executable_statistics es
    INNER JOIN internal.executables e ON es.executable_id = e.executable_id
    OUTER APPLY (
        SELECT Warnings = CASE WHEN COUNT(1) = 0 THEN 'No warnings'
            ELSE CONCAT(CAST(COUNT(1) AS VARCHAR), ' warnings across ', CAST(COUNT(DISTINCT(em.subcomponent_name)) AS VARCHAR), ' subcomponents')
            END
        FROM internal.event_messages em
            INNER JOIN internal.operation_messages om
                ON em.event_message_id = om.operation_message_id
        WHERE em.operation_id = es.execution_id
            AND em.execution_path = es.execution_path
            AND om.message_type = 110
        ) w
    OUTER APPLY (
        SELECT RowsWritten = SUM(CAST(SUBSTRING([message], CHARINDEX('wrote', [message]) + 6, CHARINDEX('rows.', [message], CHARINDEX('wrote', [message])) - (CHARINDEX('wrote', [message]) + 6)) AS INT))
        FROM internal.event_messages em
            INNER JOIN internal.operation_messages om ON em.event_message_id = om.operation_message_id
        WHERE em.operation_id = es.execution_id
            AND em.execution_path = es.execution_path
            AND om.[message] LIKE '% wrote % rows%'
        GROUP BY em.operation_id, em.execution_path
        ) rw
WHERE es.execution_id = @ExecutionId
    AND NOT EXISTS (        /* Ignore root / containers */
        SELECT 'exists'
        FROM internal.executable_statistics x
        WHERE es.execution_id = x.execution_id
            AND x.execution_path LIKE es.execution_path + '\%'
        )
ORDER BY es.start_time;