Sql-Server

如何查詢 SSISDB 以找出包中的錯誤？

  • April 10, 2018

我看過這個問題 SSIS 2012 - How to Query Current Running Packages in T-SQL?

它給了我以下腳本：

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;

但它並沒有回答我的要求。我正在調查包失敗的原因，我需要掌握錯誤消息。

我在哪裡可以找到它？

我想使用 T-SQL 來查詢錯誤資訊。

我在下面也有這個腳本，它讓我很接近，但不完全是：

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 = 'InfoGroup Feed.dtsx'
--WHERE execution_path LIKE '%<some executable>%'
ORDER BY message_time DESC

這是我要處理的電子郵件，他們是如何收到該錯誤消息的：

歡迎提供有關如何解決 SSIS 錯誤的任何資訊。

在此處輸入圖像描述

我有一些我使用的查詢。一般概念是包含資訊的表是catalog.operation_messages並且您對 120（錯誤）類型的事件感興趣。

根據您要建構的查詢的健壯程度，以下兩個派生表可能也很有趣。

--- http://technet.microsoft.com/en-us/library/ff877994.aspx
-- This query translates the message_type from SSISDB.catalog.operation_messages
-- into useful text
SELECT
   D.message_type
,   D.message_desc
FROM
(
   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')
) D (message_type, message_desc);


-- Where was the error message generated?
SELECT
   D.message_source_type
,   D.message_source_desc
FROM
(
   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);

我使用這樣的查詢來查找有關錯誤的資訊。也許我只關心錯誤是什麼（查詢1）。其他時候，我想知道所有失敗的操作的所有活動（查詢 2）。一般來說，我很懶，想查看有關最後一次失敗操作的所有資訊（查詢 3 並註意警告）。

-- 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;

-- 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
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
   );

也許我很懶惰，不想在發生故障時查找此資訊，就像您的團隊似乎所做的那樣。我有一個按需執行的 SQL 代理作業，並且我有執行 SSIS 包的作業設置為在發生故障時執行該作業。

DECLARE
   @profile_name sysname = 'SQLAdmins'
,   @recipients varchar(max) = 'billinkc@kfc.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;

隨意調整

引用自：https://dba.stackexchange.com/questions/118737

相關問答

Sql-Server

SSIS - ROLLBACK TRANSACTION 請求沒有對應的 BEGIN TRANSACTION

  • March 23, 2022
檢視問答
Sql-Server

SSIS 將平面文件單元格轉換為 HH:MM:SS

  • March 22, 2018
檢視問答
Sql-Server

從具有不同使用者權限的儲存過程執行 SSIS 包

  • March 28, 2017
檢視問答
Sql-Server

將 NULL 傳遞給 SSIS 變數

  • November 20, 2016
檢視問答
Sql-Server

如何在 SSIS 包中實現合併語句？

  • September 21, 2016
檢視問答
Sql-Server

sql server max memory 包括 SSIS？

  • July 28, 2015
檢視問答