DTSX_Package_Last_Execution

Posted onAuthorStormAdminLeave a comment

Snippet Name: DTSX_Package_Last_Execution

Tags:

Created Date: 2025-06-16 13:12:41
Last Modified Date: 2025-05-31 10:31:50

Dependencies:

DBA Notes:

Sample Output: 

Title: DTSX Package Last Execution
Author: user
in SQL Server, can you create a query for me that shows the each dtsx pacakge, adn the last time it was executed

Author: ChatGPT
To retrieve information about each DTSX package and the last time it was executed in SQL Server, you can query the `SSISDB` database. Specifically, you can look into the `executions` and `packages` tables to extract package names and their last execution time.

Here's a query that should help:

```sql
USE SSISDB;
GO

SELECT 
    p.name AS PackageName,
    MAX(e.end_time) AS LastExecutionTime
FROM 
    catalog.packages p
JOIN 
    catalog.operations o ON p.project_id = o.project_id
JOIN 
    catalog.executions e ON o.operation_id = e.operation_id
WHERE 
    o.object_type = 20  -- Object type 20 is for packages
    AND o.operation_type = 200  -- Operation type 200 is for executions
GROUP BY 
    p.name
ORDER BY 
    LastExecutionTime DESC;
```

### Explanation:
- **`catalog.packages`** contains information about the packages.
- **`catalog.operations`** tracks different operations, including package executions.
- **`catalog.executions`** contains details about the execution of SSIS packages, including start and end times.
- The `MAX(e.end_time)` function retrieves the last time each package was executed.

You can adjust this query if you need more specific details (e.g., for a particular project or folder).

Author: user
your schema defonitiosn seem to be invaldi, i get two errors: Msg 207, Level 16, State 1, Line 10
Invalid column name 'project_id'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'operation_id'.


Author: ChatGPT
It seems I made an assumption about the structure of your SSISDB catalog that doesn't match your environment. Let's adjust the query to align with the typical structure of the SSISDB tables.

Here's a corrected query that will return the name of each package and the last time it was executed:

```sql
USE SSISDB;
GO

SELECT 
    pkg.name AS PackageName,
    MAX(ex.start_time) AS LastExecutionTime
FROM 
    catalog.executions ex
JOIN 
    catalog.event_messages em ON ex.execution_id = em.operation_id
JOIN 
    catalog.packages pkg ON ex.package_name = pkg.name
WHERE 
    em.message_type = 120  -- Message type 120 indicates a package start event
GROUP BY 
    pkg.name
ORDER BY 
    LastExecutionTime DESC;
```

### Explanation of changes:
1. **`catalog.executions`**: This table contains execution records. I use `execution_id` to link it to event messages.
2. **`catalog.event_messages`**: This contains messages generated during the execution, including when the package starts and completes. The `message_type = 120` filters for events where the package execution started.
3. **`catalog.packages`**: This table links the `package_name` to the actual packages in the system.

Let me know if this gives better results, or if there are still issues, and I'll further refine the query!

Author: user
your assistance helped me build a better query. I think the query i am pasting below does a better job of identifying all packages and their last execution date. additionally, some of the views in the catalog schema returned no rows, while the tables in the internal schema returned data in my environment, so I am sticking to the concrete tables:  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @FolderName VARCHAR(128)  = 'WorkForce'

SELECT 
  [fldz].[name] AS [FolderName],
  [prjz].[name] AS [ProjectName],
  [pkz].[name] AS [PackageName],
  MAX([opers].[start_time]) AS [LastExecutionTime]
FROM [SSISDB].[internal].[packages] [pkz]
  INNER JOIN [SSISDB].[internal].[projects] [prjz]  ON [pkz].[project_id] = [prjz].[project_id]
  INNER JOIN [SSISDB].[internal].[folders] [fldz]   ON [fldz].[folder_id] = [prjz].[folder_id]
  LEFT JOIN [SSISDB].[internal].[executions] [ex]   ON [ex].[project_name] = [prjz].[name] AND [ex].[package_name] = [pkz].[name]
  LEFT JOIN [SSISDB].[internal].[operations] opers  ON ex.[execution_id]= [opers].[operation_id]
WHERE  ([fldz].[name] = @FolderName OR @FolderName = '')
GROUP BY 
  [fldz].[name],
  [prjz].[name],
  [pkz].[name]
ORDER BY [LastExecutionTime]



Author: ChatGPT
User's environment returns data from the SSISDB internal schema tables rather than the catalog views, so they are using tables from the `internal` schema for SSIS queries.

Author: tool
Model set context updated.

Author: ChatGPT
Your query looks well-constructed and efficiently pulls the relevant data. Using the internal schema tables like `[SSISDB].[internal].[packages]`, `[projects]`, `[folders]`, and `[executions]` should give you more reliable results, especially since the catalog views didn't return data in your environment.

It seems like you've incorporated a dynamic folder filter with `@FolderName`, which is a great addition for flexibility. Your joins and groupings also ensure that you get the last execution time for each package, grouped by folder and project.

If you need further refinements or adjustments in the future, feel free to ask!

Author: user
so i have a few edge cases, where a package has an execute package task, and the package called does not show up in my query above. do you know how to add those executions? will tehy appear in a different internal table?

Author: ChatGPT
Yes, when a package contains an **Execute Package Task**, the execution of the child package is treated as a separate operation in SSIS. However, it may not be directly linked in the same way as the parent package execution.

To capture child package executions, you will need to check for executions triggered by the parent package. The child package executions should still be recorded in the `[SSISDB].[internal].[executions]` table, but they may not have the same `project_name` and `package_name` as the parent package execution. These executions are typically recorded as independent executions with their own execution IDs.

To detect and include the child package executions, you could try the following approach:
- Identify any child package executions (possibly by correlating execution times).
- Join against the `internal.executable_statistics` table, which may contain more granular details about individual executable tasks (including Execute Package Tasks).

Here???s a query you can use as a starting point:

```sql
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @FolderName VARCHAR(128)  = 'WorkForce';

SELECT 
    fldz.name AS FolderName,
    prjz.name AS ProjectName,
    pkz.name AS PackageName,
    MAX(opers.start_time) AS LastExecutionTime
FROM SSISDB.internal.packages pkz
    INNER JOIN SSISDB.internal.projects prjz ON pkz.project_id = prjz.project_id
    INNER JOIN SSISDB.internal.folders fldz ON fldz.folder_id = prjz.folder_id
    LEFT JOIN SSISDB.internal.executions ex ON ex.project_name = prjz.name 
        AND ex.package_name = pkz.name
    LEFT JOIN SSISDB.internal.operations opers ON ex.execution_id = opers.operation_id
    LEFT JOIN SSISDB.internal.executable_statistics es ON ex.execution_id = es.execution_id 
        AND es.executable_type = 'ExecutePackageTask'  -- Identifying Execute Package Task
WHERE 
    (fldz.name = @FolderName OR @FolderName = '')
GROUP BY 
    fldz.name,
    prjz.name,
    pkz.name
ORDER BY 
    LastExecutionTime;
```

### Key Details:
- **`internal.executable_statistics`** table: This stores execution details for each task inside a package, including `Execute Package Task`. The `executable_type` for such tasks is `'ExecutePackageTask'`.
- By joining with this table, you can capture information about packages that are executed as part of another package's task.

This query should help to incorporate executions of packages that are invoked via the **Execute Package Task**. Let me know if you need further adjustments!

Leave a Reply