SSIS 中的查找转换

SSIS 中的查找转换是最有用的转换之一，它用于将源数据与参考表（或查找表）中的现有数据进行比较并找到匹配项。此 SSIS 转换允许您执行数据查找。对于不匹配的行，我们可以做更多的工作。

区分大小写：SSIS 中的查找转换区分大小写、假名和重音。查找转换无关紧要，即使您的源数据库和目标数据库设置不区分大小写。因此，如果您的 SSIS 数据大小写不正确，建议将查找表和源数据转换为大写或小写。请参阅《转换大小写敏感性》一文以更好地理解。

SSIS 查找转换编辑器选项

双击 SSIS 查找，这将打开转换编辑器进行配置。通用页面提供了设置缓存模式和连接类型以及指定如何处理没有匹配项的行的选项。

查找仅支持两种类型的连接管理器

• OLE DB 连接管理器：SSIS 查找转换使用 OLE DB 连接管理器 来访问存在于 SQL Server、Oracle 和 DB2 中的参考表。请参阅查找转换部分，了解如何使用 OLE DB 连接管理器配置查找转换。
• 缓存连接管理器：SSIS 查找默认使用 OLE DB 连接管理器连接到参考表。可以使用其他源，但它们必须通过缓存转换间接使用。例如，使用缓存连接管理器，我们可以使用文本文件或 Excel 文件作为查找表。请参阅《完整缓存模式下的转换》一文，了解如何使用 缓存连接管理器 配置查找转换。有关更多转换 >> 点击此处。

SSIS 查找转换缓存模式

您有三种 SSIS 查找缓存模式选项

• 完整缓存模式：这是查找转换中最常用的方法。如果我们选择此选项，整个查找（或引用）表将预加载到缓存（内存）中。转换将从内存而不是数据集执行查找。当查找表中的行数较少时，它效果很好。
• 部分缓存模式：如果我们选择此选项，SSIS 查找转换将以空缓存开始转换。当新行从数据流传入时，查找转换首先在其缓存中查找匹配的值。如果缓存中找不到，它将检查查找表。如果表中找到匹配项，则该值将被缓存（存储在内存中）以供下次使用。如果查找表很大，可以使用此方法。
• 无缓存模式：如果我们选择此选项，查找转换将在任何阶段都不使用缓存来存储查找表。当新行从数据流传入时，它会直接在查找表中查找匹配的值。

SSIS 中的错误处理

此页面上的最后一个选项是指定如何处理没有匹配项的行。SSIS 查找转换为您提供了四个选项

• 组件失败（默认）：当新行从数据流传递到查找时。如果它在查找表中找不到任何匹配的行，它将导致包失败。
• 忽略失败：如果我们选择此选项，当新行从数据流传递时，即使查找转换在查找表中找不到任何匹配的行，它也会继续处理。
• 将行重定向到不匹配输出：如果我们选择此选项，查找转换会将与查找表中的行不匹配的行重定向到“不匹配”。在实际操作中，我们主要使用此选项。
• 将行重定向到错误输出：如果我们选择此选项，SSIS 查找转换会将找不到任何匹配行的行重定向到标准错误输出。

提示：在“高级”页面中，我们可以调整查找转换的缓存属性。

SSIS 查找转换示例

本节将通过一个示例说明如何使用 OLE DB 连接管理器在 SSIS 中执行查找转换。在此示例中，我们将使用 OLE-DB 连接连接到 SQL Server 并执行查找转换操作。

在创建查找转换包之前，让我们看一下表及其数据。

现在，让我们也看一下源数据。

配置 SSIS 中的查找转换

步骤 1：将“数据流任务”从工具箱拖放到控制流区域。接下来，将其重命名为“查找转换”。

双击它，它将打开数据流选项卡。

步骤 2：将 OLE DB 源 从工具箱拖放到数据流区域。双击数据流区域中的 OLE DB 源以打开连接管理器设置。

从上面的屏幕截图中，您可以发现我们在 SQL Server 中选择了以下数据库作为源数据库，并将“[源]”作为源表。

步骤 3：单击列选项卡以验证列。在此选项卡中，我们还可以取消勾选不需要的列。

单击“确定”，然后将 SSIS 查找转换从工具箱拖放到数据流选项卡。

步骤 4：双击查找转换将打开编辑器。使用此编辑器配置查找表（参考表）。

在 SSIS 查找转换的“通用”选项卡中，“指定如何处理没有匹配项的行。”将默认的“组件失败”更改为“将行重定向到不匹配输出”选项。将此选项更改为将所有不匹配的行重定向到“不匹配”输出。否则，包将失败。

在 SSIS 查找转换连接类型部分下，我们选择了 OLE DB 连接管理器，因为我们使用此表作为参考。

如果使用任何缓存文件 (.caw)，请使用缓存连接管理器。请参阅完整缓存模式文章，了解如何使用缓存文件执行这些操作。

单击 SSIS 查找转换的连接选项卡

步骤 5：如果您已经创建了 OLE DB 连接管理器，请从列表中选择它；否则，请单击“新建”按钮进行配置。这里我们选择了“教程数据库”。

在本例中，我们将参考表用作查找或参考表。因此，源数据将与此表中提供的数据进行比较。

提示：我们不应将整个表用作查找的参考。也许您应该使用命令来选择您想要引用的所需列名。

步骤 6：单击“可用 SSIS 查找转换输入列”。拖放到可用查找列以创建两个数据集之间的连接。

源数据中的国家列将针对查找表中的“[国家名称]”列进行检查。

• 如果源数据中的国家列的每一行都与查找表“[国家名称]”列的任意一行匹配，则该行将传递到匹配输出。
• 如果它与查找表中“[国家名称]”列的任意一行都不匹配，则该行将传递到不匹配输出。

有时，SSIS 查找表中可能有多余的列，我们希望将它们传递到匹配输出。在这些情况下，我们必须像对 Cid 列那样，勾选可用查找列中的该列。

步骤 7：将两个 OLE DB 目标 拖放到数据流区域。然后，将输出箭头拖到这个新的 OLE-DB 目标。将出现一个弹出窗口，“输入输出选择”窗口，提示您选择输入输出类型。您可以选择“查找匹配输出”或“不匹配输出”。选择“查找匹配输出”并单击“确定”。

当您将另一个输出箭头拖到 OLE DB 目标 1 时，它会自动分配“查找不匹配输出”。

步骤 8：双击 OLE DB 目标并打开编辑器。选择 OLE-DB 连接管理器和数据库中的“[匹配的行]”表来存储匹配的行数据。

步骤 9：单击“映射”选项卡，检查 SSIS 查找转换源列是否已正确映射到目标列。

步骤 10：现在，双击 OLE DB 目标 1 以配置“不匹配”输出。在这里，我们将选择数据库中的“[未匹配的行]”表来存储不匹配的行数据。

重复步骤 9 并单击“确定”以完成查找转换包的创建。

右键单击解决方案资源管理器中的“SSIS 包”中的“查找转换”，然后选择“执行包”。

正如您所见，我们的 SSIS 查找转换包已成功执行。让我们打开 Management Studio 查看匹配的行数据。

让我们看看查找转换的不匹配行数据。