Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 566
BI Dude Avatar asked Apr 29 '14 14:04

BI Dude


People also ask

How do I find out what SSIS packages are running in SQL Server?

Open SQL ServerManagement Studio. In Object Explorer, expand the Integration Services node, right-click SSISDB, and then click Active Operations.

How can we review status of SSIS package?

The All Operations report shows an operation performed on the SSIS catalog, such as executing package executions or validations packages, deploying, and server start-up. Each report also has a filter link, which enables you to filter information based on date, status, package name, project name, ID, and folder name.


2 Answers

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
like image 75
Kyle Hale Avatar answered Oct 02 '22 07:10

Kyle Hale


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;
like image 29
billinkc Avatar answered Oct 02 '22 07:10

billinkc