Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Find out which SSIS package(s) are executed more than once

Discussion in 'Business Intelligence' started by Peter Schmitz, May 22, 2017.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    We're currently working at a customer who have hundreds of SSIS packages making up their data warehouse. In the past, various developers have worked off and on on the solution, and the customer wanted to make sure that they had an overview of any SSIS packages that are executed in more than 1 other package (basically the general idea is to ensure that long-running packages are run as little as possible).

    In order to try and extract that information from SSISDB, rather than manually going through dozens of packages (and most likely overlooking something on more than one occasion), I built the following script.

    I know this looks far from optimized, but the script is built te way it is on purpose. The script would take a long time to execute, and thus I broke it up into multiple smaller steps. I also added some DISTINCT clauses in order to get rid of the records.

    Hopefully this is helpful for others as well :)

    Code (TSQL):

    DECLARE @packages TABLE
    (
        package_name sysname
        , executable_name sysname
        , executable_id int
    );

    INSERT INTO @packages
    SELECT
        package_name
        , executable_name
        , executable_id
    FROM
    (
        SELECT  
            package_name
            , executable_name
            , executable_id
        FROM
            SSISDB.[catalog].[executables]
        WHERE
            executable_name
                /* Find the packages that execute other packages */
            IN (SELECT DISTINCT(REPLACE(Package_name, '.dtsx', '')) FROM SSISDB.[catalog].[executables])
    ) a
    WHERE
        /* Remove the record where the package records itself being executed */
        executable_name != REPLACE(Package_name, '.dtsx', '')
    ;

    DECLARE @packages2 TABLE
    (
        executable_name sysname
        , package_name_1 sysname
        , package_name_2 sysname
        , executable_id int
    );

    INSERT INTO @packages2
    SELECT
        DISTINCT
        a.executable_name
        , a.package_name
        , b.package_name
        , MAX(a.executable_id)
    FROM
        @packages a
            /* Find where the same package is executed from more than 1 other package */
        JOIN @packages b ON a.executable_name = b.executable_name AND a.package_name != b.package_name
    GROUP BY
        a.executable_name
        , a.package_name
        , b.package_name

    SELECT
        'SSIS' AS source_system
        , a.*
        , es2.execution_duration / 1000 duration_in_seconds
    FROM
        @packages2 a
            /* Get execution time of the last run of the package */
            JOIN (SELECT executable_id, MAX(execution_id) as max_execution FROM SSISDb.catalog.[executable_statistics] GROUP BY executable_id) es
            ON a.executable_id = es.executable_id
            JOIN SSISDb.catalog.[executable_statistics] es2 ON es.executable_id = es2.executable_id AND es.max_execution = es2.execution_id

     

Share This Page