SSIS学习使用三：Integration Services增量加载之新增数据
翻译参考
本文主要参考翻译自 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 表结构如下，同时对其他字段添加和源表一样的约束：
CREATE TABLE [dbo].[FullName]( [BusinessEntityID] [int] PRIMARY KEY, [FirstName] [nvarchar](50) NOT NULL, [MiddleName] [nvarchar](50), [LastName] [nvarchar](50) NOT NULL ) 复制代码
模拟源表新数据
接下来，先模拟增加新行(有两种办法，一种是向源表插入数据，实现新增行；另一种是从目标表删除一些数据，这样源表多出的数据就是新数据【实现不变更源表数据】)。
如下，删除目标表中MiddleName是NULL的数据，执行后一共删除8499行
Use AdventureWorks2012 go Delete dbo.FullName Where MiddleName Is NULL 复制代码
查找在一个表但不在另一个表中的数据
增量加载新行的第一步是监测新行。有以下几种方法实现：
方法一：查找在源表但是不在目标表中的行。如下T-SQL语句：
Select BusinessEntityID, FirstName, MiddleName, LastName From Person.Person where BusinessEntityID not in ( select BusinessEntityID from dbo.FullName ); 复制代码
查找在一个表但不在另一个表的前提是两个表可以唯一确定一条数据，否则没法比较。
方法二：通过左连接 LEFT JOIN 查询查找不在目标表中的数据
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查询：
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列的值不存在于目标表。如果在源表而不在目标表，那么就是一个新行 —— 上一次加载后添加到源表中的行。这就是想要加载的新行。
查看数据流路径
右键源和查找转换之间的数据流路径，点击"编辑"。
在数据流路径编辑器中的元数据页，可以看到，它的配置 和 查找转换与目标之间的数据流路径 的 元数据是一样的。
查找转换的无匹配输出是查找转换输入的精确拷贝。这个场景就是：如果没有发现匹配，则要发送经过查找转换的无匹配输出的所有数据列，以便于被下游（downstream）使用。
关闭数据流路径编辑器，现在的数据流任务是这样的。
运行及多次执行
通过按F5键或菜单上的"启动调试"按钮，将会看到：
仅仅加载了8499新行（这是上面模拟新行时删除的行数）。
下面就可以重新执行加载（re-execute the load）。在debug按钮旁边有重新启动的按钮。 SSIS包停止又再次执行，这次查找转换在目标表中找到了所有的行。没有发送到查找转换的“无匹配输出的行”。
总结
此处已经完成了几个目标。
首先，创建一个加载器，该加载器仅将新行从源表添加到目标表。其次，构建的加载器是可重新执行的，它不会将行的重复副本堆积到目标表。
刚刚构建了的SSIS包是“函数性”的（functional，表示的是可重复执行的）。许多在生产环境中的 SSIS包 包含执行相似加载的数据流任务。在增量加载中仅加载新行的场景：比如一个包括历史每日货币兑换率的表（数据不会随着时间而改变）；还有保存每天温度(高温)的表（数据也是永不会更新）等。
但是对于频繁更新源数据的情况，当前的增量加载明显就没那么灵活了！
作者：代码迷途
链接：https://juejin.cn/post/7027285038290960414