Understanding the SSISDB Catalog in SQL Server Integration Services (SSIS)
The SSISDB catalog serves as the central repository for managing and interacting with SSIS projects deployed to an Integration Services server. In this catalog, users can configure environments, manage parameters, execute and troubleshoot packages, and oversee server operations. The catalog is essential for running and managing Integration Services (SSIS) projects.
Key Components of the SSISDB Catalog
The SSISDB catalog contains various objects necessary for integration services management:
- Projects: Contains all the SSIS projects that are deployed.
- Packages: Includes details of all SSIS packages stored in the catalog.
- Parameters: Defines project and package parameters.
- Environments: Specifies runtime values for packages via environment configurations.
- Operational History: Tracks the execution history of packages and projects.
Important SSISDB Catalog Views
1. Executions
The executions view displays details about the instances where packages have been executed within the catalog. When a package is executed using the Execute Package task, it runs within the same execution instance as the parent package.
2. Folders
The folders view lists all folders within the SSISDB catalog, which help organize projects and packages.
3. Packages
The packages view shows all the packages registered in the catalog.
4. Projects
The projects view contains details about each project in the catalog, including project names and metadata.
Common SQL Queries to Manage and Troubleshoot SSISDB
1. View Executions Details
This SQL query retrieves execution details for packages within the SSISDB catalog:
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
FROM SSISDB.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;
2. Find Error Messages by Package
This query allows you to locate error messages associated with a specific package:
SELECT q.*
FROM (SELECT em.*
FROM SSISDB.catalog.event_messages em
WHERE em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
AND event_name NOT LIKE '%Validate%') q
WHERE package_name = 'MainPackage.dtsx'
ORDER BY message_time DESC;
3. Find All Error Messages
This query fetches all error messages related to the operations:
SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type,
OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
WHERE OM.message_type = 120;
4. Find Messages Associated with Failing Operations
This query retrieves messages linked to failing operations:
SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type,
OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
INNER JOIN (
SELECT DISTINCT OM.operation_id
FROM catalog.operation_messages AS OM
WHERE OM.message_type = 120
) D ON D.operation_id = OM.operation_id;
5. Find Messages Associated with the Last Failing Run
This query retrieves messages related to the last failing operation:
SELECT OM.operation_message_id, OM.operation_id, OM.message_time, OM.message_type,
OM.message_source_type, OM.message, OM.extended_info_id
FROM catalog.operation_messages AS OM
WHERE OM.operation_id =
(SELECT MAX(OM.operation_id) FROM SSISDB.catalog.operation_messages AS OM
WHERE OM.message_type = 120);
6. Send Email with Query Results
If you want to send the query result via email, you can use the following query:
DECLARE @profile_name sysname = 'SQLProfile',
@recipients varchar(max) = 'recipient@example.com',
@subject nvarchar(255) = 'Failed Package Alert',
@body nvarchar(max) = 'An SSIS package has failed. Please check the logs.',
@query nvarchar(max) = 'SELECT O.object_name AS FailingPackageName, ...'
EXECUTE msdb.dbo.sp_send_dbmail @profile_name, @recipients, @subject, @body, @query;
