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

翻译参考

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

感谢支持！

更新行的增量加载

Updating Rows in Incremental Loads 增量加载中更新行

在上一部分（新增数据的增量加载）中，我们实现了一个可重新执行的SSIS包。通过构建数据流任务实现仅加载新行。

下面的内容，介绍实现对更新行的增量加载功能（将源表中更新的行加载到目标）。

模拟更新代码

和上一篇"新增行的增量加载"中一样。通过代码修改目标表dbo.FullName，实现数据更新的效果。

打开SSMS，连接 SQL Server 后执行如下语句：

Use AdventureWorks2012
go
Update dbo.FullName
 Set MiddleName = 'Ray'
 Where MiddleName Is NULL

执行该语句（F5），会有8499条记录在目标表中更新。这样目标表与源表的数据有所不同，实现源表中数据有更新的效果。

实现更新数据的增量加载

修改查找转换目标表的返回信息

打开 SQL Server Data Tools 和之前的 SSIS解决方案 FirstSSIS，在数据流选项卡下，此处需要修改下查找转换（Lookup Transformation）。

双击“查找转换”，打开查找转换编辑器：

在"列"选项中，之前我们定义BusinessEntityID列在可用输入列和可用查找列之间的映射。查找类似于 Join 查询：两列的连接线意味着 join 中 ON 子句，它定义了执行查找功能的匹配规则。

之前我们没有使用复选框选中任何可用查找列。如果查找转换类似于Join，则这些复选框就是从连接表中添加列到 SELECT子句 的机制。

点击可用查找列中Head标题"名称"左侧的复选框，即"全选"。此时，可用输入列和可用查找列的下方可以看到"查找列"(Lookup Column)、"查找操作"(Lookup Operation)、"输出别名"(Output Alias)的表格。

输出别名用来替代从可用查找列返回的列的字段名。同样类比于JOIN，输出别名类似在使用JOIN语句的SELECT子句中用AS给列起别名。为了标识出从查找操作返回的行，作者经常使用“LkUp_”、“Dest_”别名前缀，用来区分来自 OLE DB源 和来自 查找转换 的列。另外，如果列名一样的话，SSIS会在列名后追加"(1)"。

如下，是使用“LkUp_”前缀的截图：

让我们回顾下。

OLE DB源 从 Person.Person 表加载进入 数据流(Data Flow)，然后通过 数据流管道 进入 查找转换流，目标表dbo.FullName 通过 T-SQL查询 访问。查找转换打开目标表，并尝试匹配存在于 数据流管道中 和 目标表中 的记录。当没有发现匹配时，非匹配行发送到"无匹配输出"(No Match Output)。

我们修改了查找转换的配置，当查找转换在 源(可用输入列) 和 目标表的BusinessEntityID列 中发现匹配时，会从目标表中返回 BusinessEntityID、FirstName、LastName和MiddleName所有列 的值。

注：此处直接进行 全列 匹配，也可筛选出 新增的行、更新的行 这两种数据，通过无匹配输出即可输出出来。

但是更新和插入需要执行不同的语句，如果这么处理，就要更新 OLE DB目标 中的插入方式，改为SQL语句，其中判断不存在插入存在更新（又多了一次判断）。更重要的是这种情况无法使用批量更新（批量更新可大幅度提高执行速度，减少运行时间）。

因此，后面的有条件拆分等十分有必要和正确，也可以更深入了解其他组件！

添加命令和有条件拆分转换，实现增量更新

在数据流画布中中拖拽一个 "OLE DB命令"转换(OLE DB Command Transformation) 和一个 "有条件拆分"转换(Conditional Split Transformation)。

点击"查找"转换，拖动绿色的数据流路径到"有条件拆分"。"查找"转换剩下的这条唯一的绿色数据流路径是"查找匹配输出"(Lookup Match Output) —— 所以此时不会弹出提示选择。除了包含的数据外，查找转换的 "匹配输出" 和 "不匹配输出" 之间还有其他区别，最大的不同是列。

添加到源行中的查找列

下面看一下被查找转换的输出添加到源数据行中的查找列。

先看一下输入列，右键 OLE DB源 和 查找转换 之间的数据流路径，点击"编辑"。点击数据流路径编辑器的 "元数据"(Metadata) 页，可以看到路径元数据表格，它们是从 OLE DB源 进入 查找转换 的列。

关闭数据流路径编辑器。

右键"查找无匹配输出"(Lookup No Match Output)的数据流路径，位于 查找转换 和 OLE DB目标(名字为"FullName") 之间，然后点击"编辑"，在数据流路径编辑器中点击 "元数据"(Metadata)页。

可以看到，查找转换无匹配输出的元数据和查找转换输入的元数据是一样的。这是设计好的：查找仅仅传送流经转换没有匹配的行。

关闭数据流路径编辑器。

因为上面配置了查找转换的原因，发现匹配的记录有一些不同：来自目标的额外的列被返回。右键"查找匹配输出"(Lookup Match Output)数据流路径（位于查找转换和有条件拆分转换之间）。

在“元数据”中，可以很容易地看出来添加到数据流路径中的额外列 —— 尤其是我们添加了别名前缀标识从查找转换的匹配操作返回的列。然后关闭编辑器。

在新行数据的增量加载中，我们配置查找无匹配输出，同时没有去看从查找表返回的列，是因为新行的增量加载不需要它们。但此处，我们需要它们比较源和目标表的列值，借此，我们可以检查变更。

变更检测（Change Detection）

变更检测是 ETL 的一个子科学。此处讨论的方法是一个好的开始，记住，我们正在演示一个原理。

"查找匹配输出"（Lookup Match Output）中的匹配(“Match”)的含义是什么？

它意味着在 源表(Person.Person，通过OLE DB源适配器加载进入数据流任务) 中的 BusinessEntityID列 与 目标表(dbo.FullName，通过查找转换访问) 中的BusinessEntityID列有相同的值。我们知道BusinessEntityID列值匹配，但是不确定其他的列是否匹配。

如果所有的源和目标列值匹配，那么源记录没有改变 —— 可以考虑排除它。如果源发生了改变，我们需要捕获这些改变并复制到目标。这是 增量加载更新(Incrementally Loading updates) 的目标。

这里分为两个部分：首先检测不同，然后应用更新。

在 SSIS 中完成变更检测

编辑有条件拆分

打开“有条件拆分”转换编辑器(双击或右键Conditional Split Transformation)。

"有条件拆分"转换编辑器分为三部分，左上部分包括两个虚拟文件夹：变量和参数(Variables and Parameters)、列(Columns)

如上，展开"列"文件夹，我们需要通过比较 FirstName, LastName 和 MiddleName 列来检测源和目标表中的不同。BusinessEntityID列已经在查找(Lookup)内部匹配过了。

首先比较 FirstName 列，点击"列"列表中 FirstName，拖拽它到有条件拆分转换编辑器下面部分的"条件"列(Condition column)中。

当你释放 FirstName 列到"条件"列后，你点击任何地方都会发生验证。此处文本是红色的，是因为当前示例(case)验证失败。

此处为什么验证失败？条件必须是布尔值(True或False)。但是 FirstName 是一个字符串值。如果点击确定按钮关闭有条件拆分转换编辑器，会得到非常有用的错误消息：

还没有完成。在"有条件拆分转换编辑器"的右上部分包含SSIS表达式语言语法（SSIS Expression Language syntax）

SSIS表达语言学习起来比较难。

我们需要检测 FirstName 不等于 LkUp_FirstName 的行。在"SSIS表达式语言"部分，展开 运算符(Operators) 文件夹并选择 不等于运算符(unequal operator)!=。

点击不等于运算符拖拽到列“子条件表达式(case Condition expression,编辑器下面的部分)” 中 FirstName 列的右侧。

下面，拖拽"列"文件夹下的 LkUp_FirstName 到列 子条件表达式 中不等于运算符的右侧。

因为在 例子条件字段(case Condition field) 中表达式现在是一个布尔值，验证通过，文本显示黑色。

例子条件(case Condition)检查什么？它检查在源和目标表BusinessEntityID列匹配时，来自一行数据的 FirstName 值和 LkUp_FirstName 值；如果 FirstName 列不同，则源和目标是不同的。因为目标通常落后源，因此假定源是新的、更好的和更精确的数据。这种情况下，我们需要获取数据到目标中。

因为在之前的测试设置查询中没有对 FirstName 列做任何改变（只修改了 MiddleName 列的值）。此处 FirstName 列的不等于测试将总是返回False。值不是不相等是因为都相等。

我们需要添加例子条件表达式(case Condition expression)去捕获所有的变更。

如下，通过使用小括号包裹条件表达式，实现隔离这部分的变更检测条件表达式(change-detection Condition expression)

下面需要添加 MiddleName 列的测试条件。

此处需要先思考下，我们要查找的是什么？我们检查在源和目标列值之间任何的不同。如果一个变更发生，就可以触发一个对目标的更新。如果多于一个变更发生，其中的一个足够触发更新。因此，我们应该检查一列或另一列之间的不等式(inequality)。最后语句中的运算符是"Or"。

为了表示逻辑OR(To represent Logical OR)，将 SSIS表达式语言运算符 列表滚动到底部，拖动 ||(逻辑或)（Logical OR）到例子条件表达式中闭合小括号的右边。

然后在条件OR运算符的后面添加小括号()。

从编辑器左上部分的"列"文件夹中拖动 MiddleName 列到小括号中，拖动一个 不等于 SSIS表达式语言运算符到 MiddleName 列的右边，然后拖动 LkUp_MiddleName 列到 不等于 运算符和小括号结束符之间，如下：

此时再次看到了例子条件验证。条件测试 FirstName 和 LkUp_FirstName 列的不同，或者 MiddleName 和 LkUp_MiddleName 的不同。

接下来，添加 或者 LastName 不等于 LkUp_LastName 的逻辑实现。最后case Condition expression显示如下：

(FirstName != LkUp_FirstName) ||( [MiddleName] != [LkUp_MiddleName] )||( [LastName] != [LkUp_LastName] )

最后，在关闭"有条件拆分转换编辑器"之前，重命名下输出(Output)名称"大小写1"(英文为Case 1，此处的中文很不恰当)为Updated Rows。(同时，可根据个人喜好去除表达式中不必要的方括号和空格)。

此处 case Condition 或 case Condition Expression 中的 case 翻译为"情况"可能更合适些。

修正例子条件表达式case Condition Expression

此时的 case Condition Expression 在运行时会发生错误，原因在于在表的定义中 MiddleName 是可空的，而且该列也确实存在空值。当两个NULL值进行相等（或不相等）比较时，返回值仍为NULL，会导致该条件表达式的值最后为NULL，而不是 True 或 False。产生报错无法执行。另外两列定义为非空值，无需考虑。

如下，借助 SSIS条件表达式 的 ISNULL 函数实现对 MiddleName 为空时的处理（去除了空格或方括号）：

(FirstName!=LkUp_FirstName) ||
( (ISNULL(MiddleName)&&!ISNULL(LkUp_MiddleName)) || 
  (!ISNULL(MiddleName)&&ISNULL(LkUp_MiddleName)) ||
  (!ISNULL(MiddleName)&&!ISNULL(LkUp_MiddleName)&&MiddleName!= LkUp_MiddleName) 
) ||
(LastName!=LkUp_LastName)

小总结

我们完成了什么？来自查找转换匹配输出的行流入“有条件拆分转换”(Conditional Split Transformation)，也就是每一行中 BusinessEntityID 列和 LkUp_BusinessEntityID 列有相同的数据(这两个列在查找转换中用来执行匹配操作)。在有条件拆分中配置了一个条件并命名为“Updated Rows”。Updated Rows 条件会捕获行，行中 FirstName，MiddleName 或 LastName 三列值中的一个(多个，或全部)与对应的“LkUp_”前缀的副本之间存在不同。

很重要的一点是：有条件拆分转换转移行到不同的输出。当像上面那样定义一个条件，同时也就创建了一个新的输出(output) —— 一个新的路径 —— 有条件拆分转换(Conditional Split Transformation)从这里输出。

那么，源列值与其匹配的目标列值相同的行呢？这是一个很好的问题。在刚刚定义的 “Updated Rows” 条件网格的下面，你将看到"默认输出名称:"。

这是在"有条件拆分转换" 中发送不符合条件的行的地方。

点击"确定"（OK），关闭编辑器。

点击"有条件拆分"转换，拖动绿色数据流路径到 "OLE DB命令"(OLE DB Command)，出现提示后，选择来自条件拆分的 "Updated Rows" 输出。

现在数据流任务看起来是这样的：

OLE DB命令转换(OLE DB Command Transformation)

OLE DB命令转换 可用来执行SQL命令

接下来使用 OLE DB命令转换 将目标表与源表匹配行中不同的行进行更新。

上面我们已经检测出源表中不同于目标表中的行，并通过 "Updated Rows"输出 发送这些更新行到 OLE DB命令转换 中。下面配置下 "OLE DB命令" 的更新功能。

双击 "OLE DB命令" 打开 "OLE DB命令的高级编辑器"(the Advanced Editor for OLE DB Command)。

在 "连接管理器"(Connection Managers) 标签页，设置 连接管理器下拉列表 为 "[Server-Name].AdventureWorks2012.sa"，点击 "组件属性"(Component Properties) 页并滚动到 SqlCommand属性，点击 SqlCommand属性值文本框 的省略号，打开 字符串值编辑器(String Value Editor)，输入下面的T-SQL语句：

Update dbo.FullName 
  Set FirstName = ?
, MiddleName = ?
, LastName = ?
 Where BusinessEntityID = ?

注意编辑器中SQL命令每行要有空格或符号

在 OLE DB 中，问号(?) 表示参数占位符。我们将会在下一个标签页映射这些参数。点击确定(OK)，关闭字符串值编辑器。

点击 "列映射"(Column Mappings) 标签页。

问号(?)标记是基于0的数组，即 Param_0 代表第一个问号...。我们通过从 "可用输入列"(Available Input Columns) 中逐一的拖拽字段到 "可用目标列"(Available Destination Columns) 中的参数实现映射。来自源和目标表的列存在于 "可用输入列" 中。前缀“LkUp_” 的列包含目标数据（它是在查找转换中返回的）。由于我们仅仅想映射来自源表的列（通常假定源中包含必须要更新的数据）。第一个问号表示 FirstName，因此 FirstName列 映射 Param_0，MiddleName列 映射 Param_1, LastName列 映射 Param_2, BusinessEntityID列 映射 Param_3：

此时已经完成 参数映射 和 OLE DB命令 的配置。点击确定(OK)，关闭编辑器。

此时数据流任务应该是这样的

执行更新行的增量更新

点击 "启动调试"按钮(或按F5) 运行SSIS包。

在 SSIS 项目的 "进度" 中(退出Debug运行模式后，该标签名会变为"执行结果")，可以看到调试运行的状态。

如上，从开始到结束用了4秒多。

在 OLE DB命令 中的更新语句是花费时间最多的。原因是 OLE DB命令 每次处理一个行，这样类似于一个游标(cursor)。基于行(row-based)的操作是很慢的。

基于集合的更新(Set-Based Updates)

有没有方法可以避免基于行的操作呢？答案是有的。

添加OLE DB目标

停止SSIS包的运行，在数据流任务中点击 "OLE DB命令"(OLE DB Command) 并删除。

从工具栏拖动一个 "OLD DB目标"(OLD DB Destination) 到该位置，并连接 "有条件拆分" 的 "Updated Rows"输出。

右键 OLD DB目标 重命名为 "StageUpdates"，并双击打开该目标编辑器。连接管理器选择[server-name].AdventureWorks2012，数据访问模式为 "表或视图——快速加载"(Table or View – Fast Load)。

然后，点击 “表或视图的名称” 下拉菜单旁边的 “新建” 按钮，打开创建表的对话框

首先去除 “LkUp_” 前缀的列，只存储来自源的用于更新的数据，并修改其他列定义：

CREATE TABLE [StageUpdates] (
    [BusinessEntityID] [int] PRIMARY KEY,
    [FirstName] [nvarchar](50) NOT NULL,
    [MiddleName] [nvarchar](50),
    [LastName] [nvarchar](50) NOT NULL
)

单击确定，StageUpdates表将会在上面选择的 "OLE DB连接管理器" 连接的数据库中创建（此处为AdventureWorks2012数据库）。

点击 "映射"页(Mappings page)。

可以看到列自动映射，因为列名和数据类型匹配。点击确定(OK)，关闭编辑器。

添加执行SQL任务(Execute SQL task)

下面我们需要管理 StageUpdates 中的行。上面的操作更新行都进入了 StageUpdates 表中，我们需要将其应用到 dbo.FullName 表中。

点击 "控制流"标签(Control Flow tab)，从左侧工具箱添加一个 执行SQL任务(Execute SQL task) 到画布。点击 数据流任务(data flow task) 并将绿色的 "优先约束"(Precedence Constraint) 连接到 "执行SQL任务"。

我们在 "执行SQL任务" 中执行一个基于集合的更新。它可以做到一次更新所有，而不是通过循环每次更新一条记录。

双击 "执行SQL任务" 打开 执行SQL任务编辑器。在 “常规”(General) 页中，改变 name属性 为“Apply Staged Updates”，Connection属性 为 "[Server-Name].AdventureWorks2012.sa"。点击 SQLStatement属性 的省略号，打开 "输入SQL查询"(Enter SQL Query) 窗口，输入下面的语句：

Update dest
 Set dest.FirstName = stage.FirstName
  , dest.MiddleName = stage.MiddleName
  , dest.LastName = stage.LastName
 From dbo.FullName dest
   Join dbo.StageUpdates stage
    On stage.BusinessEntityID = dest.BusinessEntityID

该语句 联接(joins) StageUpdates表 和 dbo.FullName表，实现从 stage 到 destination(目标) 的更新。

执行基于集合的更新

在执行当前SSIS包之前，让我们先重置下目标数据库的表，模拟源和目标表之间的不同。如同最开始做到，在 SSMS 执行如下 T-SQL 语句：

Use AdventureWorks2012
go
Update dbo.FullName
 Set MiddleName = 'Ray'
 Where MiddleName Is NULL

返回 Data tool 并执行 SSIS 包，控制流和数据流如下：

在 "进度"(Progress) 标签页中，可以看到执行时间的提升。由 4.812秒 提升到 0.718秒。

管理StageUpdates表

完成后需要清空Stage

我们仍需要管理 StageUpdates 表。当前的配置将永远持续堆积要更新的记录到 StageUpdates 表中。

我们需要在应用更新到 dbo.FullName 目标表后从 StageUpdates 中删除记录。下面是为什么我不这么做的原因：如果在执行过程中发生了一些“不良”事件，那么在执行之间挂起这些记录（hanging，或者保留这些记录），可以让我再有一个数据点来检查线索。

因此，我们 在数据流任务加载表之前截断该表 —— 在数据流任务执行之间将记录保留在 StageUpdates 表中。

加载之前截断Stage

停止 SSIS 的调试。然后拖拽另一个 "执行SQL任务"(Execute SQL Task) 到控制流画布，从新的 “执行SQL任务” 连接绿色的 "优先约束" 到数据流任务

双击 执行SQL任务 打开编辑器，配置 "常规"页，设置如下：

这样就可以准备下一次的测试。

按 F5或"启动调试"按钮，执行SSIS包。

总结

本文在 增量加载新增数据 的基础上，讨论了 检测源与目标之间的差异，并将更新应用到目标。同时讨论了 基于行的更新 和 基于集合的更新(速度快很多)

下一步，将实现，删除目标中那些已经在源中删除的行。