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
Open SQL ServerManagement Studio. In Object Explorer, expand the Integration Services node, right-click SSISDB, and then click Active Operations.
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.
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
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With