This website requires JavaScript.

SSIS:捕获修改了的数据

获取修改了的数据一般有三种方式:

1.使用一个datetime列

缺点:是并不是每个表都会有个‘修改日期’字段来让你判断行是否修改过

使用实例可以参考我之前的文章:SSIS: 使用最大ID和最大日期来增量更新表

 

2.MSSQL 自带的功能CDC (change data capture)

CDC使用 SQL Server Agent把变更的数据写到另外一个表中。

缺点: 如果数据库变动频繁会占用大量的磁盘空间

1) 启用CDC

注意:只有开发板和企业版提供CDC功能

USE [AdventureWorksDW2012] GO

EXEC sys.sp_cdc_enable_db GO

2) 开启DimAccount的CDC
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name   = 'DimAccount',
@role_name     = 'MyRole',
@filegroup_name = 'Primary',
@supports_net_changes = 1
GO
此时我们可以看到System Tables里面多了很多表

image

其中 cdc.dbo_DimAccount_CT 就是记录变更的表

接着我们改变DimAccount表中的一条记录,再查看 cdc.dbo_DimAccount_CT 表

我们发现他复制了整行修改前的记录和修改后的记录

image

其中 _$operation列是我们操作的顺序,可以残出来那个是旧的哪个是新的。

image

禁止DimAccount的 CDC

EXECUTE sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'DimAccount', @capture_instance = N'dbo_DimAccount';

 

3.MSSQL 自带的功能 change tracking

只记录修改的key ,大多数情况下这个方法不错。 更改记录可以设置定期删除。

1) 开启CHANGE_TRACKING功能

USE [AdventureWorksDW2012]

ALTER DATABASE AdventureWorksDW2012
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);--自动清理前两天的记录

ALTER TABLE  [dbo].[DimAccount]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
2) 同上面一样修改Dimaccount并查看效果

这里变更记录并不是存放在表里面,而是通过一个函数获得

USE [AdventureWorksDW2012]

SELECT * FROM CHANGETABLE(CHANGES [dbo].[DimAccount], 0) AS T

如图 AccountKey 被记录了下来

image

与CDC比较

image

 

参考文档

sys.sp_cdc_disable_table (Transact-SQL)

https://msdn.microsoft.com/zh-cn/library/bb510702

0条评论
avatar