SSIS 2012 - How do I query currently packages in T-SQL?

Question

I want to know which packages are currently running on the system.

Currently, I can find running packages by opening the inline report in Integration Services.

I would like to know what the request is behind the scenes?

Read more: I use the project deployment model

Answer 1

If you use the IS directory to manage your packages, you can check the running packages with this request in the database of your directory (SSISDB is used by default):

select * from catalog.executions where status = 2

UPDATE: To see all packages called by a specific execution:

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

Answer 2

AS N West , catalog.executions , - NULL end_time

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

, , , , - MasterFacts, MasterDimensions .., .

//, ( ), , .

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;

Answer 3

SSIS. , Integration Services, , catalog.packages SSISDB. , , catalog.executions.

MSDN Integration Services:

http://technet.microsoft.com/en-us/library/ff878135.aspx