Integration Services Level 4"/>
[译]Stairway to Integration Services Level 4
在本文中, 我们说下增量更新数据:即将数据源中更新了的数据替换掉目标表中对应的数据.
更新代码
操作之前我们先把目标表e (dbo.Contact). 的数据改掉
Use AdventureWorks go Update dbo.ContactSet 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.ContactSet 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.ContactWhere FirstName Is NullSelect *From Person.ContactWhere MiddleName Is NullSelect *From Person.ContactWhere 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 destSet dest.FirstName = stage.FirstName, dest.MiddleName = stage.MiddleName, dest.LastName = stage.LastNameFrom dbo.Contact destJoin dbo.StageUpdates stageOn stage.Email = dest.Email
再改动以下dbo.Contact 表, 然后执行SSIS包:
Use AdventureWorks go Update dbo.ContactSet 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
至此增量更新搞定.
原文地址: +Series/76390/
资源:
点此下载项目文件
更多推荐
[译]Stairway to Integration Services Level 4
发布评论