This website requires JavaScript.

[译]Stairway to Integration Services Level 5 - 增量删除数据

在 dbo.Contact中添加一行记录

Use AdventureWorks go Insert Into dbo.Contact (FirstName, MiddleName, LastName, Email) Values ('Andy', 'Ray', 'Leonard', 'andy.leonard@gmail.com')

把 Data Flow Task 改名为 “Insert and Update Rows.” 然后再拖一个 Data Flow task 并改名为 “Delete Rows”如下图:

image 图 3

打开 Delete Rows,添加 OLE DB Source ,配置如下:

  • OLE DB Connection Manager: (local).AdventureWorks
  • Data Access Mode: Table or view
  • Name of the table or view: dbo.Contact image 图 4

我们使用 dbo.Contact 作为源,在添加一个 Lookup Transformation 来判断缺少的记录. :

image 图 5

打开,把“Specify how to handle rows with no matching entries” 设定为Redirect rows to no match output”:

image 图 6

在 Connection 页,  “ 选择 “(local).AdventureWorks”. 选择“Use results of an SQL query” 选项,并且输入以下T-SQL语句 :

Select EmailAddress As Email From Person.Contact

Column界面,还是通过Email来匹配:

image 图 7

拖个 OLE DB Destination ,然后连接Lookup, 选择提示框选择 Lookup No Match Output :

image 图 8

把 OLE DB Destination 改名为 “StageDeletes” 双击打开.设置和之前一样. 建立表格如下:

CREATE TABLE [StageDeletes] (
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Email] nvarchar(50),
    [MiddleName] nvarchar(50)
)
[![image](http://images.cnitblog.com/blog/378031/201409/231512103571082.png "image")](http://images.cnitblog.com/blog/378031/201409/231512095604439.png)
点开Mappings.已经自动匹配 .:

image 图 9

和之前一样,我们要放个Execute  SQL Task  处理删除动作:

image

图 10

打开 Execute SQL Task Editor  设置属性如下:

  • Name: Apply Staged Deletes
  • Connection: (local).AdventureWorks
  • SQLStatement:    (这里原文应该写错了.不是 Person.Contact,而是 dbo.Contact)
Delete src
 From Person.Contact src
   Join StageDeletes stage
     On stage.Email = src.EmailAddress
右击Apply Staged Deletes 然后点 Execute Task执行单元测试:

image 图 11

最后在 Truncate StageUpdates  里面加上StageDeletes的Truncate语句  :

Truncate Table StageDeletes
[![image](http://images.cnitblog.com/blog/378031/201409/231512142952986.png "image")](http://images.cnitblog.com/blog/378031/201409/231512137015871.png) _图 __12_

执行这个SSIS包:

image Figure 13

检查 Delete Rows Data Flow Task:

image 图 14

看来没什么问题.  在SQL中执行语句来验证下.  (原文又写错了…应该是dbo.Contact) :

Use AdventureWorks go Select Count(*) As RecCount From dbo.Contact Where FirstName = 'Andy' And LastName = 'Leonard'

 

原文地址: http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/76395/

资源: 项目文件

0条评论
avatar