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

翻译参考

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

感谢支持！

删除行的增量加载

Deleting Rows in Incremental Loads 增量加载中删除行。

在上一篇文章中我们学习了如何从源到目标传输更新，同时也介绍使用基于集合的更新来优化实现。

本文我们在 SSIS 中继续构建增量加载功能，重点实现当加载到目标时删除那些已经从源中移除的行。

为了完成在增量加载中删除行，基本上在随后的数据流任务中修改了很多Update操作。

模拟源表中删除行

开始设置测试条件。源表中删除行表示行在目标表中但是不在源表中。也就是，之前插入到目标中的行已经从源中移除。

然后在SSMS中执行下面的T-SQL，增加4条语句

Use AdventureWorks2012
go
Insert Into dbo.FullName
 (BusinessEntityID,FirstName, MiddleName, LastName)
Values
 (20778,'Andy', 'Ray', 'Leonard'),
 (20779,'Candy', 'Ray', 'Leonard'),
 (20780,'Beany', 'Ray', 'Leonard'),
 (20781,'Andy', 'Tony', 'Leonard');

当执行增量加载删除逻辑时它应该检查源中丢失的记录并从目标中删除这些行。

此处，你可以运行现在的SSIS包，检查下在目标表新增的记录对已有的处理增量加载的逻辑有什么影响。

删除行的增量加载实现

在向 SSIS 包添加组件之前，首先重命名下 "数据流任务data flow task" 为 "Insert and Update Rows"。

添加删除的数据流任务

拖拽另一个数据流任务(Data Flow task) 到控制流，并从 "Apply Staged Updates"执行SQL任务中添加一个 "优先约束"(Precedence Constraint) 到这个新数据流任务。重命名数据流任务为 "Delete Rows"。

添加OLEDB源

打开数据流"Delete Rows" 的数据流任务，添加一个 OLE DB源并打开编辑器，配置如下属性：

OLE DB连接管理器: [Server-Name].AdventureWorks2012.sa
数据访问模式: 表或视图(Table or view)
表或视图名: dbo.FullName

添加查找转换

当前数据流任务中使用目标表FullName 作为源，下一步是使用查找转换(Lookup Transformation) 删除该源中丢失的行。这个处理过程和上一个数据流任务是一样的。

拖拽一个查找转换到 "Delete Rows"数据流任务。连接从 "OLE DB源"适配器到 "查找" 的数据流路径。

打开查找转换编辑器，在"常规"页中，修改 "指定如何处理无匹配项的行"(Specify how to handle rows with no matching entries) 下拉列表为 “将行重定向到无匹配输出”("Redirect rows to no match output")，缓存模式可以根据实际设置。

在"连接"页，确保 "OLE DB连接管理器" 下拉列表设置为"[Server-Name].AdventureWorks2012.sa"。选择 "使用SQL查询结果"(Use results of an SQL query) 选项并在 textbox 中输入如下T-SQL语句

Select BusinessEntityID As BID
 From Person.Person

为了检测出在源表中已经被去除，而在目标表中存在的行，只需要指定 "BusinessEntityID" 字段即可。在"列"页面中，从可用输入列格子中拖拽 "BusinessEntityID"列到可用查找列格子：

因为我们配置查找重定向不匹配的行到无匹配输出("常规页"中)，这样，如果在源(dbo.FullName) 和查找(Person.Person) 之间没有匹配，则行将会被发送到"无匹配输出"。点击确定按钮，关闭编辑器。

添加OLE DB目标

如何实现实际的删除操作？如同之前的练习一样，在原来的更新逻辑中，我们通过添加和配置一个 OLE DB命令转换(OLE DB命令转换) 来实现。此处也可以这样操作。但是你应该已经知道了最后的结果。因此我们跳过中间的这步（可以尝试自己实现）。添加一个 OLE DB目标到 "Delete Rows"数据流任务。然后连接来自查找转换的无匹配输出。

重命名目标为 "StageDeletes" 并双击打开编辑器。选择 "OLE DB连接管理器"。点击 "表或视图的名称"(Name of the table or view) 下拉列表旁边的新建按钮(New button)，修改显示的DDL语句如下:

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

点击确定，将会创建 StageDeletes 表。然后点击 "映射" 页完成 OLE DB目标的(自动映射,auto-mapped)配置

点击OK按钮，关闭 OLE DB目标编辑器。

基于集合的删除

和之前的更新一样，我们需要应用"基于集合的删除"，使用类似基于集合和相关的语句实现。

在控制流中，添加一个执行SQL任务(Execute SQL Task) 并连接来自 "Delete Rows"数据流任务的绿色 "优先约束"。

打开 Execute SQL Task Editor，修改下面的属性：

Name: Apply Staged Deletes
Connection: [Server-Name].AdventureWorks2012.sa
SQLStatement：

Delete src
 From dbo.FullName src
   Join StageDeletes stage
     On stage.BusinessEntityID = src.BusinessEntityID

点击"确定"，关闭"执行SQL任务编辑器"(Execute SQL Task Editor)。

单元测试（`unit-test`）

在继续之前，让我们对该配置进行单元测试。

如何单元测试SSIS任务？右键点击 "Apply Staged Deletes"执行SQL任务，点击 "执行任务"(Execute Task)

如果我们配置正确将会正确执行。

管理StageDeletes表

停止SSIS的调试，如同基于集合的更新逻辑，我们需要管理 "StageDeletes" 表。

和之前一样，我们在每次加载之前截断(truncate) 它、将其加载到数据流任务、在加载过程的中间保留表中的记录以防我们需要查看。

不需要为此创建单独的执行SQL任务(Execute SQL Task)。我们已经有一个截断的执行SQL任务 —— "Truncate StageUpdates"，双击它打开编辑器，在已有SQL语句上添加下面的T-SQL语句：

Truncate Table StageDeletes;

关闭输入SQL查询窗口。然后修改下 Name 属性为 "Truncate StageUpdates and StageDeletes"，点击"确定"，关闭编辑器。

执行删除数据的增量更新

按"F5"(或"启动调试"按钮)执行 SSIS 包。检查执行的控制流和 "Delete Rows" 数据流任务

多出的4行已经从目标表(dbo.FullName) 中删除。可以在SSMS中，通过下面的SQL语句验证：

 select * from  dbo.FullName f
 left join Person.Person  p
 on p.BusinessEntityID=f.BusinessEntityID
 where p.BusinessEntityID IS NULL;

【此处也可以直接使用 where 条件判断是否已经删除】

返回结果数为0。

总结

我们已经构建了第一个 SSIS设计模式(Design Pattern) —— 增量加载。同时学习了一些 ETL测试入门示例，了解了查找转换和执行SQL任务。

我们设计和构建了一个可重新执行的SSIS包，并结合了"增量加载"。可以每月执行一次，加载上个月更改的记录；也可以很方便的每五分钟执行一次，获取期间的数据变更。

非常灵活！

SSIS学习使用五：Integration Services增量加载之删除数据