[译]Stairway to Integration Services Level 4 - 更新数据

在本文中, 我们说下增量更新数据:即将数据源中更新了的数据替换掉目标表中对应的数据.

更新代码

操作之前我们先把目标表e (dbo.Contact). 的数据改掉

Use AdventureWorks
go
Update dbo.Contact
 Set MiddleName = 'Ray'
 Where MiddleName Is NULL

打开 My_First_SSIS_Project. 点击%26nbsp; Data Flow 标签. 我们把Level 3的项目文件修改下.双击 Lookup Transformation 打开 Lookup Transformation Editor:

%26nbsp;
图 1

点击Columns 页面.

在前篇文章中, 我们映射了Available Input Columns和Available Input Columns的Email字段进行内联匹配.%26nbsp;

在前一篇文章中我们没有勾选任何复选框.%26nbsp; 如果把Lookup Transformation 当做 join看待, 选了这些字段就好像是在Select 子句里面增加字段 .%26nbsp; 全选(点上面的Name) :

%26nbsp;
图 2

全选以后如图3 . Output Alias 列是就是 Available Lookup Columns输出列,类似Join以后返回的列. 我喜欢在添加前缀 %26ldquo;LkUp_%26rdquo; 或者 %26ldquo;Dest_%26rdquo; 这样方便区分来自 OLE DB Source 和 Lookup Transformation字段. 如果列名相同SSIS会后面加个%26ldquo;(1)%26rdquo;.%26nbsp;%26nbsp; 下图是添加了%26ldquo;LkUp_%26rdquo;前缀的截图:

%26nbsp;
图 3

现在让我们回顾一下 .

我们把Person.Contact 表中的记录载入Data Flow ,然后与 Lookup Transformation 中载入的 dbo.Contact进行记录匹配. 如果记录未匹配,则进行No Match Output 输出. 接着改动 Lookup Transformation 的配置, 如果发现匹配行,就输出%26nbsp; Email, FirstName, LastName, 和 MiddleName 列.

现在拖一个 OLE DB Command Transformation 和 Conditional Split Transformation . 点击%26nbsp; Lookup Transformation然后把蓝色的 Data Flow Path拖到 Conditional Split:

%26nbsp;
图 4

因为之前Lookup No Match Output 已经拖到 Contact , 所以只剩下Lookup Match Output ,这次拖到 Conditional Split就没提示选择 .Lookup Transformation 的No Match Output 和 Match Output .除了数据内容外,还有其他不同,但是最要的是字段不同.

右击OLE DB Source adapter 与 Lookup Transformation之间的Data Flow Path:

%26nbsp;
图 5

点击 Metadata 页,显示如下:

图 6

这些列来自OLE DB Source ,关掉这个 Data Flow Path 然后右击 OLE DB Destination (Contact) 与 Lookup Transformation之间的Data Flow Path,Metadata 页显示如下:

图 7

上一篇文章我们看过这个. Lookup Transformation的No Match 输出和输入的metadata其实是一模一样的.%26nbsp; 也就是说如果没有匹配,只是简单把输入的字段直接输出.

这个章节我们改动了Lookup Transformation配置,因此匹配的记录Metadata会有所不同 : 目标表的字段会附加上去 .右击 Conditional Split Transformation 和Lookup Transformation 之间的 Data Flow Path . Metadata显示如下.

%26nbsp;
图 8

因为之前选择字段的时候我们采用的别名,因此很容易的看到附加字段.

还记得在上篇文章中,我们配置了 No Match Output,但并未从被查询的表中返回字段么? 因为增量添加记录不需要这些字段,不过我们现在需要用来对比字段值.%26nbsp;

%26nbsp;

在SSIS中进行变更检测

打开 Conditional Split Transformation%26nbsp; 可以看到两个虚拟目录: Variables 和 Columns:

图 9

点开 图9的 Columns 目录. 我们准备比较 FirstName, LastName, 和 MiddleName 字段.

先来比较 FirstName 字段. 点击 FirstName 然后拖到下面的列表中 :

图 10

松开鼠标以后你会发现 刚拖过去的FirstName因为验证失败而变红. 为什么会验证失败? 条件必须进行布尔运算 .但是FirstName是字符值,所以未进行判断. 错误提示如下:

%26nbsp;
图 11

右上角是条件表达式语句%26nbsp;

SSIS 表达式语句有点难学,你可以看下我以前写的博文,应该对你有所帮助.

我要检查 FirstName%26nbsp; 不等于 LkUp_FirstName. 在表达式区域点开 Operators 虚拟目录然后选择 unequal 操作符:

%26nbsp;
图 12

点击 Unequal 操作符,然后拖到 FirstName 字段右边:

%26nbsp;
图 13

接着把, LkUp_FirstName 拖过去 :

%26nbsp;
图 14

由于现在表达式计算结果为布尔值,所以整个条件表达式又变为黑色.

因为我们没有改过FirstName字段的值,所以当我们执行测试的时候. FirstName判断值总为False(我们只改了MiddleName 值) . 接着把其他几个字段也放进去判.最后把 Case 1 改名为 Updated Rows. 如下图

%26nbsp;
图 21

注意,每新建一个条件(condition) . Conditional Split Transformation 就会生成一个新的Output.用来传送数据

其中还有个 Default Output Name 如下图, 如果没有条件被匹配,那么记录会从这个output走

%26nbsp;
图 23

关掉Conditional Split Transformation ,然后拖动蓝色路径到 OLE DB Command. 选择Updated Rows 输出路径 :
图 24

现在你的Data Flow 应该显示如下:

%26nbsp;
图 25

我们用OLE DB Command Transformation 来对目标表进行更新操作. 双击打开OLE DB Command Transformation , 在Connection Manager 里面选择(local).AdventureWorks ,然后在 Manager Component Properties 标签的 SqlCommand%26nbsp; 属性中输入以下SQL语句

Update dbo.Contact
  Set FirstName = ?
, MiddleName = ?
, LastName = ?
 Where Email = ?

图 26

接着我们还要映射参数占位符( parameter placeholders%26nbsp; 就是问号(?)) ,点击 Column Mappings 标签:

%26nbsp;
图 27

? 标记是以0开始的数组. 也就是说%26nbsp; Param_0 对应第一个问号,Param_1%26nbsp; 代表第二个 ,以此类推. 我们把相对应的字段都拖到参数进行映射 .

%26nbsp;
图 28

关掉OLE DB Command. 现在 Data Flow Task 应该和下图一样:

%26nbsp;
图 29

按 F5 测试下结果:

%26nbsp;
图 30

报错了%26hellip;点开Process 标签.发现一些错误. 不过读起来比较困难.

图 31

如果我们可以右击错误,然后复制文本:

%26nbsp;
图 32

错误信息如下:

[Conditional Split [2]] Error: The expression "(FirstName != LkUp_FirstName) || (MiddleName != LkUp_MiddleName) || (LastName != LkUp_LastName)" on "Conditional Split.Outputs[Updated Rows]" evaluated to NULL, but the "Conditional Split" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row).%26nbsp; The expression results must be Boolean for a Conditional Split.%26nbsp; A NULL expression result is an error.

错误的原因是我们表达式产生了NULL值. 表达式里面只要有一个比较是NULL, 那结果就不会是布尔值 Ture 或 False .

让我们来看看 Person.Contact里面哪个字段有空值:

Use AdventureWorks
go
Select *
 From Person.Contact
 Where FirstName Is Null

Select *
 From Person.Contact
 Where MiddleName Is Null

Select *
 From Person.Contact
 Where LastName Is Null

执行这些语句以后发现l 8,499 行的MiddleName 是空值. 其实就是我们之前更改的那些字段.

让哦们来解决这个问题. 通过IsNull() 函数来判断MiddleName是否空值,返回布尔结果. 语句更新如下:

(FirstName != LkUp_FirstName) || ((ISNULL(MiddleName)?"Humperdinck": MiddleName) != (ISNULL(LkUp_MiddleName) ? "Humperdinck": LkUp_MiddleName)) || (LastName != LkUp_LastName)

图 33

有个重要的地方注意下:%26ldquo;Humperdinck%26rdquo; 永远是个无效值.%26nbsp; 如果源表中的MiddleName 由Null更新成了Humperdinck . 这时候 (ISNULL(MiddleName) ? "Humperdinck" : MiddleName)%26nbsp; 结果是Humperdinck . 而 (ISNULL(LkUp_MiddleName) ? "Humperdinck" : LkUp_MiddleName)) 的结果也是Humperdinck. 表达式根本无法检测不同.%26nbsp;

我用%26ldquo;Humperdinck%26rdquo; 当做 middle name是因为我喜欢 The Princess Bride 这个片子. 实际操作的话我会组合数字,字母及一些很难碰到的字符组合来做匹配.%26nbsp;

按F5再测试下:

%26nbsp;
图 34

测试成功..不过我们发现更新耗时了一分三十七秒 :

%26nbsp;
图 35

为什么执行花了这么长时间? 主要OLE DB Command执行语句花费时间较长. 为什么? 因为OLE DB Command%26nbsp; 一次只更新一行. 像游标一样.

批量更新 Set-Based Updates

有没有方法避免这种单行(row-based)更新的模式? 让我们看看怎么处理.把 OLE DB Command删掉.然后托个%26nbsp; OLE DB Destination 代替它:

%26nbsp;
图 36

把OLE DB Destination 更名为%26ldquo;StageUpdates%26rdquo;.%26nbsp; 双击打开 . 确保选了 %26ldquo;(local).AdventureWorks%26rdquo;和 %26ldquo;Table or View %26ndash; Fast Load%26rdquo;.

新建表格 :

%26nbsp;
图 37

%26nbsp;

CREATE TABLE [StageUpdates] (
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Email] nvarchar(50),
    [MiddleName] nvarchar(50)
)

注意要映射字段:

%26nbsp;
图%26nbsp; 38

点击 Mappings 页:

%26nbsp;
图 39

因为Available Destination Columns和 Available Input Columns的Metadata一样,所以字段已经自动匹配.

点击确认以后如图 40:

%26nbsp;
图 40

接着我们在Data Flow Task 下面拖个 Execute SQL task ,把StageUpdates表里面的记录更新到dbo.Contact :

%26nbsp;
图 41

双击 Execute SQL Task把 Name 属性改为 %26ldquo;Apply Staged Updates%26rdquo; .%26nbsp; Connection 属性选择 %26ldquo;(local).AdventureWorks%26rdquo;. 然后在 SQLStatement 属性里面贴入以下语句:

Update dest
 Set dest.FirstName = stage.FirstName
  , dest.MiddleName = stage.MiddleName
  , dest.LastName = stage.LastName
 From dbo.Contact dest
   Join dbo.StageUpdates stage
    On stage.Email = dest.Email

再改动以下dbo.Contact 表, 然后执行SSIS包:

Use AdventureWorks
go
Update dbo.Contact
 Set MiddleName = 'Ray'
 Where MiddleName Is NULL

%26nbsp;
图 42

Data Flow task :

%26nbsp;
图 43

执行时间已改善!

%26nbsp;
图 44

%26nbsp;

我们还需要处理以下StageUpdates 表, 执行之前我们先把记录清掉. 避免执行的时候数据重复导入. 在托一个 Execute SQL Task 到 Control Flow 然后连接 Data Flow Task:

%26nbsp;
图 45

双击Execute SQL Task 配置信息如下:

%26nbsp;
图 46

再执行一次 :

%26nbsp;
图 47

%26nbsp;

%26nbsp;
图 48

至此增量更新搞定.

原文地址: http://www.sqlservercentral.com/articles/Stairway+Series/76390/

资源:

点此下载项目文件