[译]Stairway to Integration Services Level 5 - 增量删除数据
默认分类
2019-12-23
354
0
在 dbo.Contact中添加一行记录
Use AdventureWorks go Insert Into dbo.Contact (FirstName, MiddleName, LastName, Email) Values ('Andy', 'Ray', 'Leonard', 'andy.leonard@gmail.com')
打开 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
图 4
我们使用 dbo.Contact 作为源,在添加一个 Lookup Transformation 来判断缺少的记录. :
打开,把“Specify how to handle rows with no matching entries” 设定为Redirect rows to no match output”:
在 Connection 页, “ 选择 “(local).AdventureWorks”. 选择“Use results of an SQL query” 选项,并且输入以下T-SQL语句 :
Select EmailAddress As Email From Person.Contact
拖个 OLE DB Destination ,然后连接Lookup, 选择提示框选择 Lookup No Match Output :
把 OLE DB Destination 改名为 “StageDeletes” 双击打开.设置和之前一样. 建立表格如下:
CREATE TABLE [StageDeletes] ( [FirstName] nvarchar(50), [LastName] nvarchar(50), [Email] nvarchar(50), [MiddleName] nvarchar(50) )
[](http://images.cnitblog.com/blog/378031/201409/231512095604439.png)点开Mappings.已经自动匹配 .:
和之前一样,我们要放个Execute SQL Task 处理删除动作:
图 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
最后在 Truncate StageUpdates 里面加上StageDeletes的Truncate语句 :
Truncate Table StageDeletes
执行这个SSIS包:
检查 Delete Rows Data Flow Task:
看来没什么问题. 在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/
资源: 项目文件