SSIS 2012 - How to Query Currently Running Packages in T-SQL?

Question

I want to know what packages are currently running in the system.

Currently, I can find out running packages by opening the built-in report in Integration Services.

I would like to find out what the query is behind the scenes?

More details: I use Project deployment model

Solution 1

If you're using the IS Catalog for managing your packages, you can check running packages with this query against your catalog database (the default is SSISDB):

select * from catalog.executions where status = 2

UPDATE: To see all the packages a given execution invoked:

select distinct execution_path from internal.event_messages where operation_id = @executionID

Solution 2

AS N West references, the table you're interested in is catalog.executions Specifically, you are interested in anything with a NULL end_time

-- Just the basics of what is running
SELECT 
    *
FROM
    catalog.executions AS E
WHERE
    E.end_time IS NULL

That view is nice as you can see the package, the project and the folder it's executing from as we have lots of same named packages - MasterFacts, MasterDimensions, etc, that live in different projects.

If you want to tie an execution back to the folder/project/package structure because there's metadata there you are interested in (which version of the package is running), then you could use a query like this to link them.

SELECT
    E.execution_id
,   E.folder_name
,   E.project_name
,   E.package_name
,   E.reference_id
,   E.reference_type
,   E.environment_folder_name
,   E.environment_name
,   E.project_lsn
,   E.executed_as_sid
,   E.executed_as_name
,   E.use32bitruntime
,   E.operation_type
,   E.created_time
,   E.object_type
,   E.object_id
,   E.status
,   E.start_time
,   E.end_time
,   E.caller_sid
,   E.caller_name
,   E.process_id
,   E.stopped_by_sid
,   E.stopped_by_name
,   E.dump_id
,   E.server_name
,   E.machine_name
,   E.total_physical_memory_kb
,   E.available_physical_memory_kb
,   E.total_page_file_kb
,   E.available_page_file_kb
,   E.cpu_count
,   F.folder_id
,   F.name
,   F.description
,   F.created_by_sid
,   F.created_by_name
,   F.created_time
,   P.project_id
,   P.folder_id
,   P.name
,   P.description
,   P.project_format_version
,   P.deployed_by_sid
,   P.deployed_by_name
,   P.last_deployed_time
,   P.created_time
,   P.object_version_lsn
,   P.validation_status
,   P.last_validation_time
,   PKG.package_id
,   PKG.name
,   PKG.package_guid
,   PKG.description
,   PKG.package_format_version
,   PKG.version_major
,   PKG.version_minor
,   PKG.version_build
,   PKG.version_comments
,   PKG.version_guid
,   PKG.project_id
,   PKG.entry_point
,   PKG.validation_status
,   PKG.last_validation_time
FROM
    catalog.executions AS E
    INNER JOIN
        ssisdb.catalog.folders AS F
        ON F.name = E.folder_name
    INNER JOIN 
        SSISDB.catalog.projects AS P
        ON P.folder_id = F.folder_id
        AND P.name = E.project_name
    INNER JOIN
        SSISDB.catalog.packages AS PKG
        ON PKG.project_id = P.project_id
        AND PKG.name = E.package_name;

Comments

[BI Dude]

Thanks Kyle. It partly does the job. The query does show the package that is running & was executed; however, the query does not show the packages that been called by the "Parent" package.

[user3245801]

You have to use the status field to filter for running executions (you can check the executions View page specifically for the various values of status and what they mean, but 2 = running.)

[billinkc]

Right, because the parent package is running. It just happens to have a process under way that is running another package. Unless you launched that child package as a separate process, it won't have a entry in the catalog.executions. Best you'll get is to look through your operation_messages table for activity associated to the child package

[user3245801]

Updated my answer to show you how to find all the packages executed during a single execution.

[N West]

@TadasV which is why i'm not a big fan of parent/child package relationships. You could, instead, do an Execute SQL task to do a catalog.create_execution and then a catalog.start_execution. Or just use an external scheduler.