如何獲取有關 SSIS 查找中哪個查找失敗的更多資訊？

我經常遇到以下錯誤

Error: 0xC020901E at Build Test Data, Lookup ID String[120]: Row yielded no match during lookup.

為了解決這些問題，我將回到源頭並做出明智的猜測，但如果我能看到失敗的行會容易得多。

在 SSIS 中執行此操作的最佳方法是什麼（使用 MSSQL2012）？

謝謝。

我遇到的任何東西都沒有提供開箱即用的功能，如果有人證明我錯了，我會很高興。

因此，我所做的是受控失敗。

我對 DimEmployee 的查找應該總是產生匹配。在執行此依賴包之前，數據已載入到該表中。源數據沒有機會在該負載之間更改，但我仍然遇到查找不匹配的情況。不，這不是一個遲到的維度，只是他們對需求的理解很差。

無論如何，我所做的是將不匹配設置為“將行重定向到不匹配輸出”。對於 2005 年的那些人，您將不得不使用“將行重定向到錯誤輸出”

然後我計算有多少行從失敗的查找中流出，因為如果有一個失敗，可能會有更多。即使查找可以擷取該行失敗，這也是不利的 - 它只會向您顯示第一行，我通常想知道所有失敗。

受控故障

我為此使用了一個腳本任務，當我輸入它時，我可以看到如何將它變成一個可重用的組件……我的腳本任務充當一個轉換，但我可以很容易地指定一個目標。我映射到我在查找中使用的列並且沒有產生匹配。對於這個例子，我有一個EmployeeID和他們的EffectiveDT

我在這個組件中要做的是為通過的每一行觸發一個警告事件。在 SSISDB 中執行的 2012 項目的預設設置將擷取警告事件。一旦所有的行都經過，在我的PostExecute方法中，我將引發Error事件，這將導致整個 DataFlow 失敗。

using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
   /// <summary>
   /// Variable identifying whether we had any rows
   /// </summary>
   bool Found;    

   /// <summary>
   /// Initialize our variable
   /// </summary>
   public override void PreExecute()
   {
       base.PreExecute();
       this.Found = false;
   }

   /// <summary>
   /// Error out the entire task if we found any rows
   /// </summary>
   public override void PostExecute()
   {
       base.PostExecute();

       if (this.Found)
       {
           bool cancel = true;
           ComponentMetaData.FireError(0, "SCR Lookup Employee", "Unmatched Employees found. See warning for more context", string.Empty, 0, out cancel);
       }
   }

   /// <summary>
   /// Emit warnings for all the bad rows and then flag the entire operation as having bad rows
   /// </summary>
   /// <param name="Row">The row that is currently passing through the component</param>
   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       this.Found = true;
       string msg = string.Format("Research->{0}:{1}", Row.EmployeeID, string.Format("{0:yyyy-MM-dd}", Row.EffectiveDT));

       ComponentMetaData.FireWarning(0, "Unmatched Employees", msg, string.Empty, 0);
   }
}

然後我在伺服器上執行我的包並觀察執行/操作 ID。如果您進入 SSMS 中的 All Executions 報告，這是第一列 ID 中的值。在這種情況下，我看到 938，因此我在 SSISDB 中執行以下查詢

USE SSISDB;
SET NOCOUNT ON;

DECLARE
   @operation_id bigint = 938;

WITH SRC AS
(
   SELECT
       OM.message
   ,   CHARINDEX('->', OM.message) AS arrow
   ,   CHARINDEX(':', OM.message, CHARINDEX('->', OM.message)) AS colon
   ,   LEN(OM.message) AS length
   ,   RIGHT(OM.message, LEN(OM.message) - CHARINDEX('->', OM.message) -1) AS elements
   FROM
       catalog.operation_messages AS OM
   WHERE
       OM.message_type= 110
       AND OM.message_source_type = 60
       AND OM.message LIKE '%research%'
       AND OM.operation_id = @operation_id
)
, PARSED AS
(
   SELECT
       SRC.message
   ,   CHARINDEX(':', SRC.elements) AS colon
   ,   LEN(SRC.elements) AS length
   ,   SRC.elements AS RelevantText
   ,   LEFT(SRC.elements, CHARINDEX(':', SRC.elements) -1) AS EmployeeID
   ,   RIGHT(SRC.elements, LEN(SRC.elements) - CHARINDEX(':', SRC.elements)) AS EventDate
   FROM
       SRC
)
SELECT
   P.message
,   P.RelevantText 
,   P.EmployeeID
,   P.EventDate
FROM
   PARSED AS P;

這將為我提供我在“所有消息”報告中看到的所有相關位，但我可以點擊並選擇內容，並且我已經解析出我需要的內容。

message RelevantText    EmployeeID  EventDate
DFT Load FactPayroll:Warning: Research->90132693:2011-05-25 90132693:2011-05-25 90132693    2011-05-25
DFT Load FactPayroll:Warning: Research->900432371:2011-05-25    900432371:2011-05-25    100432371   2011-05-25
DFT Load FactPayroll:Warning: Research->900443209:2011-05-25    900443209:2011-05-25    100443209   2011-05-25
DFT Load FactPayroll:Warning: Research->900443418:2011-05-25    900443418:2011-05-25    100443418   2011-05-25

以互動模式執行

如果您從 Visual Studio/SSDT/BIDS 的上下文中執行它，我什至不用擔心腳本的輸出，只需添加一個數據查看器，然後您就不必擔心爬取日誌。

修改包的替代方法 (2012+)

SSIS 的 2012 版為我們提供了一個用於對已部署的包進行故障排除的新工具：Data Taps。這些是一種按執行機制，允許您在數據流中的特定點生成（僅）CSV 提取。這在高度監管的環境（SOX/SAS 70/HIPPA/PCI）中非常有用，在這些環境中，您不能在沒有地球上每個人的簽字的情況下部署更新的包。只需執行帶有特殊位集的包，它就會在眾所周知的位置生成一個 CSV。

對於上面引用的螢幕截圖，如果我想查看將要點擊“LKP DimEmployee”的所有源數據，我會辨識我的 PackagePath：\Package\DFT Load FactJobAction和我的 IdentificationString ：，Paths[ALL DateSK.Union All Output 1]然後使用 Josh 文章中的腳本

USE SSISDB;

DECLARE @ExecutionID  BIGINT 

/*****************************************************************************
--  First create an execution instance.  Data taps are valid for the specified
--  execution instance only
*****************************************************************************/
EXEC catalog.create_execution 
       N'DW Folder',  --Folder name in SSISDB 
       N'DW Project', --Project name in SSISDB
       N'FactJobAction', --Package name in SSISDB
       NULL, --optional parameter to hold reference ID for later use
       0, --optional  parameter set to 1 if 32-bit runtime required
       @ExecutionID OUTPUT;

DECLARE @DataTapID BIGINT;

/******************************************************************************
--  Next create the actual data tap.  The parameters specified below deterimine
--  at which point in a specific package the data tap will be added.  
******************************************************************************/
EXEC catalog.add_data_tap
       @ExecutionID, --output from catalog.create_execution
       N'\Package\DFT Load FactJobAction', --PackagePath property value from data flow task in SSDT
       N'Paths[ALL DateSK.Union All Output 1]', --IdentificationString property value from data flow task in SSDT
       N'File.csv', --Desired Output file name
       NULL, --optional paramter to specify number of rows to log. NULL for all rows
       @DataTapID OUTPUT; --output ID


/******************************************************************************
--  This final block of code executes the package.  The data tap file output 
--  will be found in the %SSISRoot%\DataTaps directory upon completion
******************************************************************************/
EXEC catalog.start_execution 
       @ExecutionID;  --output from catalog.create_execution

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