这是我参与11月更文挑战的第6天，活动详情查看：2021最后一次更文挑战

翻译参考

本文主要参考翻译自 the Stairway to Integration Services 系列文章的 Adding Rows in Incremental Loads - Level 3 of the Stairway to Integration Services，目的在于对 SSIS 有一个全面清晰的认识，所有内容在原文的基础上进行实操，由于版本差异、个人疑问等多种原因，未采用完全翻译的原则，同时也会对文中内容进行适当修改，希望最终可以更有利于学习和了解 SSIS，

感谢支持！

介绍

上一篇介绍了 SSIS 数据流任务加载数据的基本配置。下面将介绍增量加载(Incremental Load)，以实现上一篇创建的SSIS包可重新执行（即：可以重复执行转移增量数据[Data Rows]）。

什么是增量加载？

一个增量加载仅仅加载与上一次的加载不同的数据。

“不同”包括：

新的行 New rows
更新的行 Updated rows
删除的行 Deleted rows

本质上，增量加载是可重新执行的，这意味着可以一遍又一遍地执行加载器而不会造成损害。不仅如此，可重新执行意味着加载器被设计为可以多次执行，而不会导致服务器上不必要或重复的工作。

下面，看一下增量加载的目标：

我们要插入那些在源表中是新的但是还没有加载到目标表中的数据
我们要更新那些自上一次加载到目标表中的所有有任何更改的数据
我们要删除那些已经从源表移除的目标表中的数据

新行数据的增量加载

Adding Rows in Incremental Loads 增量加载中添加行

上一节创建的 SSIS 项目 FirstSSIS debug运行后，会把选取的数据加载到dbo.FullName表中，一共有 19972 条数据。但是，如果再次执行 SSIS 包，它将会再次加载这 19972 条数据到 FullName 表中。

而我们的需求肯定不是每次都加载重复的数据到目标表中，那么，如何实现增量加载的目标呢？

检测源表中新增的数据

修改FirstSSIS包的目标表FullName

此处我们先修改下之前的 FirstSSIS 包。原因在于，后面要实现对源表和目标表的数据的对比，而只有 FirstName、MiddleName、LastName 三个字段的 FullName 表无法唯一确定一行数据，也就无法唯一确定的比较源表和目标表中的差异，必须要引入一个唯一值(主键)字段。

FullName 表结构如下，同时对其他字段添加和源表一样的约束：

sql复制代码CREATE TABLE [dbo].[FullName](
	[BusinessEntityID] [int] PRIMARY KEY,
	[FirstName] [nvarchar](50) NOT NULL,
	[MiddleName] [nvarchar](50),
	[LastName] [nvarchar](50) NOT NULL
)

模拟源表新数据

接下来，先模拟增加新行(有两种办法，一种是向源表插入数据，实现新增行；另一种是从目标表删除一些数据，这样源表多出的数据就是新数据【实现不变更源表数据】)。

如下，删除目标表中MiddleName是NULL的数据，执行后一共删除8499行

sql复制代码Use AdventureWorks2012
go
Delete dbo.FullName
Where MiddleName Is NULL

查找在一个表但不在另一个表中的数据

增量加载新行的第一步是监测新行。有以下几种方法实现：

方法一：查找在源表但是不在目标表中的行。如下T-SQL语句：

sql复制代码Select BusinessEntityID,
FirstName,
MiddleName,
LastName
From Person.Person
where BusinessEntityID not in (
	select BusinessEntityID from dbo.FullName 
);

查找在一个表但不在另一个表的前提是两个表可以唯一确定一条数据，否则没法比较。

方法二：通过左连接 LEFT JOIN 查询查找不在目标表中的数据

sql复制代码select 
src.BusinessEntityID,
src.FirstName,
src.MiddleName,
src.LastName
FROM Person.Person src
LEFT JOIN dbo.FullName dest on src.BusinessEntityID=dest.BusinessEntityID
WHERE dest.BusinessEntityID IS NULL;

第二种方法会比第一种效率高，涉及表扫描的问题。

SSIS包中实现新增数据的增量加载

我们需要在数据流任务的 OLE DB源和 OLE DB目标(FullName) 之间添加组件，实现对增量数据的加载。

删除数据流路径

右键连接源和目标的数据流路径，点击删除。

如下，此时下侧的错误列表显示未映射的输入列错误

添加”查找”(Lookup)转换组件

从 Toolbox 中拖拽 Lookup查找的转换组件到源和目标适配器中间。

拖拽源数据流路径到 “查找”转换

查找转换（Lookup Transformation）的含义是：它在另一个表、视图或查询中匹配流入转换的行。即：转到一个表、视图或查询，查看是否在这些列中找到匹配，如果匹配则带回这些列。

此处有几个注意点：

如果在数据流和查找(Lookup)表、视图或查询之间没发现任何匹配列，默认查找转换配置使该转换失败。
如果在查找lookup表中有多个匹配，则查找转换仅仅返回第一个匹配的数据。

这些注意点是恶毒的（”vicious”），因为如果找不到匹配，操作将失败。同时当连接数据流中的行和查找(Lookup)表、视图或查询中的行时，转换仅仅返回第一个匹配行。

配置”查找”（Lookup）

双击 Lookup Transformation 打开编辑器。默认会显示常规页。

缓存模式

常规页中第一项是缓存模式（Cache Mode）属性

缓存模式控制何时以及如何执行实际的查找操作。

“无缓存”（No Cache）模式下，当每一行流经转换时，都会进行查找操作。无论何时，每行数据传入查找（Lookup）时，转换都会对查找表、视图或查询执行查询；并将任何返回的值添加到流过转换的行中。

“完全缓存”（Full Cache）模式下，在数据任务执行之前，查找操作(lookup operation)尝试从查找(Lookup)的表、视图或查询中，加载所有的行到RAM的查找缓存（Lookup cache）中。这里的“尝试”是指如果查找表、视图或查询返回一个较大的数据集，或者服务器RAM受限(低速运行或没有足够的RAM)，查找将会失败。查找缓存保存来自配置的表、视图或查询的值，在缓存中找到的匹配会添加到流经转换的行。

如果由于RAM受限在完全缓存模式下查找转换加载失败，该如何处理？一个选择是使用”无缓存”模式。第二个选择是部分缓存模式（Partial Cache mode）。

(除文中讨论的范围外，还有其他选择)

“部分缓存”（Partial Cache）模式下，转换首先在每行流过时检查“查找缓存(Lookup cache)”，查找匹配项。如果在缓存中没有匹配，会发生一个查找操作。匹配数据会添加到数据行和查找缓存中。如果查找相同匹配列的另一行数据流过转换，匹配将从查找缓存获取。

此处选择默认选项：完全缓存。因为当前Lookup查找的数据集相对较少（19972）。大数据量可以考虑其他模式。

无匹配项时的处理

下一步选择 “指定如何处理无匹配项的行”(“Specify how to handle rows with no matching entries”) 为 “将行重定向到无匹配输出”(“Redirect rows to no match output”)。

连接

点击”连接”页，设置 OLEDB连接管理器的属性为[server-name].AdventureWorks2012，和OLE DB源一样，配置一个连接管理器的接口。

下面，同样从连接管理器的SQL Server实例和数据库配置中选择一个表或输入SQL查询。此处，输入如下的T-SQL查询：

sql复制代码Select BusinessEntityID,
	FirstName,
	MiddleName,
	LastName
From dbo.FullName

进入”列”页，在”列”的上方有两个表格外观的网格。左侧的是标记为”可用输入列”(Available Input Columns)，包含进入查询转换（Lookup Transformation）的输入缓冲区的列的列表（它们是连接到OLE DB源的输出的列）。另一个表格是可用查找列（Available Lookup Columns），它们是存在于”连接”页中配置的表、视图或查询中的列（本示例为查询）

设置查找匹配条件

单击“可用输入列”中的“BusinessEntityID”列，然后将其拖到“可用查找列”中的“BusinessEntityID”列上。

此处的查找与联接(join)查询一样？将”BusinessEntityID”放到”BusinessEntityID”上时，在“可用输入列”的“BusinessEntityID”列和“可用查找列”的“BusinessEntityID”列之间出现的连线类似于联接（join）的ON子句。它定义了驱动查找功能(Lookup function)的匹配条件。

“可用查找列”包含checkbox复选框及全选的复选框。如果查找转换类似于联接（join）,则复选框就是一种用于将联接表中的列添加到SELECT子句的机制。此处不选择任何列。

我们已经配置了一个查找转换（Lookup Transformation），用以打开目标表，并将数据流管道中存在的记录与目标表中的记录进行匹配。数据流管道中存在的记录来自 OLE DB源适配器 —— 从Person.Person表加载到数据流中。目标表是dbo.FullName，我们在“查找转换”的“连接”页面上使用T-SQL查询对其进行了访问。

“查找转换”配置为通过将“目标”表中的“BusinessEntityID”列值与“源”表（通过OLE DB源适配器）中的“BusinessEntityID”列值进行比较来查找匹配项。

“查找转换”配置为发送与”查找转换的不匹配输出”不匹配的行。如果在“目标”表中的“BusinessEntityID”列值和“源”表中的“BusinessEntityID”列值之间找到匹配项，则查找转换会将这些行发送到“匹配输出”。

连接到目标

接下来继续构建增量加载。

点击”确定”(OK)按钮关闭查找转换编辑器。

然后，点击查找转换，拖动查找下面的绿色数据流路径到 OLE DB目标(名字为FullName)。出现提示框时，选择”查找无匹配输出”（Lookup No Match Output）。

回顾（review）

下面回顾下此处完成的工作，有很多很重要的点：

为什么是“查找无匹配输出”（Lookup No Match Output）。在SSIS中，查找转换（Lookup Transformation）提供这种内建的输出，捕获在源(当前例子是Person.Person表)中但不在查找表(当前例子是dbo.FullName目标表)中的记录，这就是”查找无匹配输出”。

此处原文为：the Lookup Transformation provides this built-in output to catch records in the Lookup table (the dbo.Contact destination table, in this case) that do not exist in the source (the Person.Contact table) - it's the Lookup No Match Output.。

此处应为作者笔误，因为肯定在“源”（用来查找的数据就来自于“源”，所以一定在“源”中）

为什么这里是无匹配（no match）？因为BusinessEntityID列的值不存在于目标表。如果在源表而不在目标表，那么就是一个新行 —— 上一次加载后添加到源表中的行。这就是想要加载的新行。