he SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.
The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history.
Catalog.executions: Displays the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package.
Catalog.folders: Displays the folders in the Integration Services catalog.
Catalog.packages: Displays the details for all packages that appear in the SSISDB catalog.
Catalog.projects: Displays the details for all projects that appear in 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 , 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 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;
Find error message by 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
/* Put in whatever WHERE predicates you might like*/
--WHERE event_name = 'OnError'
WHERE package_name = 'MainPackage.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC
To Find all error messages
-- http://msdn.microsoft.com/en-us/library/ff877994.aspx -- Find all error messages 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;
Find all the messages associated to failing operations
-- Generate all the messages associated 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 (
-- Find failing operations
SELECT DISTINCT OM.operation_id
FROM catalog.operation_messages AS OM
WHERE OM.message_type = 120
) D ON D.operation_id = OM.operation_id;
Find all messages associated to the last failing run
-- Find all messages associated to the last failing run
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 =
( -- Find the last failing operation
-- lazy assumption that biggest operation
-- id is last. Could be incorrect if a long
-- running process fails after a quick process
-- has also failed
SELECT MAX(OM.operation_id) FROM catalog.operation_messages AS OM
WHERE OM.message_type = 120
);
SELECT O.object_name AS FailingPackageName
, O.object_id , O.caller_name , O.server_name , O.operation_id
, OM.message_time , EM.message_desc , D.message_source_desc , OM.message
FROM SSISDB.catalog.operation_messages AS OM
INNER JOIN SSISDB.catalog.operations AS O ON O.operation_id = OM.operation_id
INNER JOIN (
VALUES (-1,'Unknown'), (120,'Error') , (110,'Warning')
, (70,'Information') , (10,'Pre-validate') , (20,'Post-validate')
, (30,'Pre-execute') , (40,'Post-execute') , (60,'Progress')
, (50,'StatusChange') , (100,'QueryCancel') , (130,'TaskFailed')
, (90,'Diagnostic') , (200,'Custom')
, (140,'DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages. The value of the message column for DiagnosticEx is XML text.')
, (400,'NonDiagnostic') , (80,'VariableValueChanged')
) EM (message_type, message_desc) ON EM.message_type = OM.message_type
INNER JOIN ( VALUES
(10,'Entry APIs, such as T-SQL and CLR Stored procedures')
, (20,'External process used to run package (ISServerExec.exe)')
, (30,'Package-level objects') , (40,'Control Flow tasks')
, (50,'Control Flow containers') , (60,'Data Flow task')
) D (message_source_type, message_source_desc) ON D.message_source_type = OM.message_source_type
WHERE OM.operation_id = (
SELECT MAX(OM.operation_id) FROM SSISDB.catalog.operation_messages AS OM
WHERE OM.message_type = 120
) AND OM.message_type IN (120, 130);
To Send email with query output
DECLARE @profile_name sysname = 'SQLProfile'
, @recipients varchar(max) = 'himanshu_patel@gmail.com'
, @copy_recipients varchar(max) = NULL
, @blind_copy_recipients varchar(max) = NULL
, @subject nvarchar(255) = 'failed package test'
, @body nvarchar(max) = 'Stuff has failed, fix please'
, @body_format varchar(20) = NULL
, @importance varchar(6) = 'NORMAL'
, @sensitivity varchar(12) = 'NORMAL'
, @file_attachments nvarchar(max) = NULL
, @query nvarchar(max) = N'
SELECT O.object_name AS FailingPackageName
, O.object_id , O.caller_name , O.server_name , O.operation_id
, OM.message_time , EM.message_desc , D.message_source_desc , OM.message
FROM SSISDB.catalog.operation_messages AS OM
INNER JOIN SSISDB.catalog.operations AS O ON O.operation_id = OM.operation_id
INNER JOIN (
VALUES (-1,''Unknown''), (120,''Error'') , (110,''Warning'')
, (70,''Information'') , (10,''Pre-validate'') , (20,''Post-validate'')
, (30,''Pre-execute'') , (40,''Post-execute'') , (60,''Progress'')
, (50,''StatusChange'') , (100,''QueryCancel'') , (130,''TaskFailed'')
, (90,''Diagnostic'') , (200,''Custom'')
, (140,''DiagnosticEx Whenever an Execute Package task executes a child package, it logs this event. The event message consists of the parameter values passed to child packages. The value of the message column for DiagnosticEx is XML text.'')
, (400,''NonDiagnostic'') , (80,''VariableValueChanged'')
) EM (message_type, message_desc) ON EM.message_type = OM.message_type
INNER JOIN ( VALUES
(10,''Entry APIs, such as T-SQL and CLR Stored procedures'')
, (20,''External process used to run package (ISServerExec.exe)'')
, (30,''Package-level objects'') , (40,''Control Flow tasks'')
, (50,''Control Flow containers'') , (60,''Data Flow task'')
) D (message_source_type, message_source_desc) ON D.message_source_type = OM.message_source_type
WHERE OM.operation_id = (
SELECT MAX(OM.operation_id) FROM SSISDB.catalog.operation_messages AS OM
WHERE OM.message_type = 120
) AND OM.message_type IN (120, 130);
'
, @execute_query_database sysname = NULL
, @attach_query_result_as_file bit = 0
, @query_attachment_filename nvarchar(260) = NULL
, @query_result_header bit = 1
, @query_result_width int = 256
, @query_result_separator char(1) = char(13)
, @exclude_query_output bit = 0
, @append_query_error bit = 0
, @query_no_truncate bit = 0
, @query_result_no_padding bit = 0
, @mailitem_id int = NULL
, @from_address varchar(max) = NULL
, @reply_to varchar(max) = NULL;
-- Send email about the failure
EXECUTE msdb.dbo.sp_send_dbmail @profile_name
, @recipients
, @copy_recipients
, @blind_copy_recipients
, @subject
, @body
, @body_format
, @importance
, @sensitivity
, @file_attachments
, @query
, @execute_query_database
, @attach_query_result_as_file
, @query_attachment_filename
, @query_result_header
, @query_result_width
, @query_result_separator
, @exclude_query_output
, @append_query_error
, @query_no_truncate
, @query_result_no_padding
, @mailitem_id OUTPUT
, @from_address
, @reply_to;
References:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15
