[译]Stairway to Integration Services Level 3

让我们打开之前的项目:My_First_SSIS_Project_After_Step_2.zip

之前项目中我们已经向dbo.contact 导入了19972行,如果再次执行包会重复导入,让我们来解决这个问题.

增量导入目标 (Incremental Load Goals)

在我们批量处理之前,先来明确下增量导入的目标:

如果数据源中有新纪录,我们要把这些记录导入目的地.
如果数据源中有记录变更,我们要把之前导入目的地的相关记录更新.
如果数据源中的记录被删除,我们要把之前导入目的地的相关记录删除.

本篇文章主要讲第一点: 把新纪录导入目的地.

我们先删掉dbo.Contact 表中一些记录,来模拟现实的场景: 数据源有的数据,但是目的地中却没有. 执行下面代码

Use AdventureWorks
go
Delete dbo.Contact
Where MiddleName Is NULL

我们把所有MiddleName 为空的值都删掉,执行语句后返回如下信息:

(8499 row(s) affected)

现在我们目的表中只有 11,473 行(原来19,972) .场景模拟达成.

首先要做的就是检测新行. 由于数据源和目的地都在同一个数据库中,所以我们可以直接写些T-SQL语句鉴别这些新行.有几个方法来达成:

方法 1: 找到存在于数据源(Person.Contact) 但是不再目的表中的数据 (dbo.Contact). 在SSMS中执行以下T-SQL 语句:

Use AdventureWorks
go
Select 
 FirstName
,MiddleName
,LastName
,EmailAddress
From Person.Contact
Where EmailAddress Not In
 (Select Email
 From dbo.Contact)

这个查询返回Person.Contact表中无法与 dbo.Contact表配的Email 地址.

方法 2: 另外一个方法是使用JOIN来获取新行:

Use AdventureWorks 
go 

Select 
src.FirstName 
,src.MiddleName 
,src.LastName 
,src.EmailAddress 
From Person.Contact src 
Left Join dbo.Contact dest 
On src.EmailAddress = dest.Email 
Where dest.Email Is NULL

这两个方法里面第二个效率高

第一个查询耗时6秒返回 8,499 行:

图 9

第二个查询返回同样的8,499行,但是耗时 0 秒:

图 10

有很多方法来判断那个查询更好,不过这个超出了我们文档的范围. (可以查看相关文章 Query Execution Plans).

现在我们开始改SSIS

回到 My_First_SSIS_Project. 你可能已经猜到,我们需要在Data Flow Task中,在OLE DB Source 和 Contact (OLE DB) Destination adapters 中间添加一个组件 .首先我们要删除连接他们的 Data Flow Path :

Figure 11

删掉以后Data Flow Path , OLE DB Destination adapter ( Contact ) 出现错误指示 - 红色的圆X :

图 12

因为我们删掉了之前配置好的 Data Flow Path ,所以提示错误了.还记得我们之前点开 Data Flow Path 看到的元数据页面么? 它是the Data Flow Task 与 the Destination adapter接口.

接口是软件开发的一个术语.接口被当做object间的协议 ("contracts" 连接,联系比较合适吧 = =).删掉Data Flow Path,之后连接就断掉了.因此The OLE DB Destination报错.

让我们进一步看下错误信息.

图 13

OLE DB Destination adapter 提示丢失了一个字段,无法定位 FirstName. 其实其他的字段也丢失了. 那为什么没有提示全部丢失的字段?其实很简单,因为它不关心多少字段丢失,它知道它需要所有的字段.所以只要有一个丢失了.它就直接报错.

添加 Lookup 组件

拖个 Lookup Transformation ,然后用 Data Flow Path连接OLE DB Source和Lookup Transformation:

图 14

顾名思义 Lookup Transformation 就是查看其他表,视图或者查询来进行行的匹配. Lookup 有一些概念我们会在使用的时候逐一交代. 虽然听上去比较简单,但是有些特殊的地方.

Quirk #1: 如果在数据流与被查找的表、视图、或是查询没有能匹配的列.那么默认情况下Lookup Transformation就会出错.

Quirk #2: 如果在被查询表中发现多个匹配记录,那么Lookup Transformation 只返回起初发现的那个.

配置 Lookup

双击 Lookup Transformation 默认显示如下.

图 15

Cache Mode 数据控制lookup 的操作方式.

Matt Masson, Microsoft开发人员及SSIS专家,写了一个文档.各位可以参考. Lookup Cache Modes. 这里我做个概括:

在 No Cache 模式中, lookup 操作是一行行查询的. 也就是说过来一行数据他就执行一次查询.

在 Full Cache 模式中, lookup 操作试图在Data Flow Task 执行前将所有被查询的表格,视图,或查询数据载入到内存 .你应该注意到了”试图(attempts)" 这个词. 如果查询的表格,视图,或查询返回大量数据, 或者内存限制(运行缓慢,或者没足够内存支持) Lookup操作就会失败.

如果在Full Cache mode中由于内存问题导致 Lookup Transformation 失败,那要怎么处理? 第一个选项是使用 Cache mode. 第二个选项是使用 Partial Cache mode. 还有其他的方法,不过不再本文探讨范围内.

在Partial Cache 模式中, Transformation 首先对Lookup cache 进行查询.如果在缓存中没有匹配到数据,就会到数据库中查询.如果查询到匹配的数据,这个数据就会添加到Lookup缓存. 后面碰到同样数据就可以直接在缓存中找到了.

本例我使用Full Cache 模式,因为我们一次只匹配小量记录19,972 条, 我会监视SSIS包的性能,如果需要的话会进行调整.

接下来,把"Specify how to handle rows with no matching entries" 改为 "Redirect rows to no match output":

图 16

点击 Connection 页面,然后把 OLE DB Connection Manager 选为 "(local).AdventureWorks". 就像OLE DB Source 适配器一样,我们在这边配置接口:

图 17

同样的,和 OLE DB Source 适配器一样,这里需要选择表或者用SQL查询访问数据.本例使用以下T-SQL 查询:

SELECT
 Email
 ,FirstName
 ,LastName
 ,MiddleName
FROM dbo.Contact

图 18

点击 Columns 页面.上方有两个表. 左边的是输入字段列(Available Input Columns ,也就是前面OLE DB Source输入的数据) , 这个里面列都会放进 Lookup Transformation 的输入缓冲 . 另外一个是Lookup字段列表(Available Lookup Columns, 就是刚才Lookup 组件连接管理器连接的数据).

点击Available Input Columns 里面的Email字段然后拖到Available Lookup Columns的 Email 字段.还记的我之前用Join 连接两表进行T-SQL查询么? 你现在在lookup里面做的拖动操作和Join类似.