SSIS: Capturing modified data

There are generally three ways to get the modified data:

1. Use a datetime column

Cons: Not every table will have a ' Date modified ' field to let you determine if the row has been modified

Use an instance to refer to my previous article: SSIS: Incrementally update tables with a maximum ID and a maximum date

2.MSSQL comes with the function CDC (change data capture)

The CDC uses SQL Server Agent to write the changed data to a different table.

Cons: If the database changes frequently consumes a lot of disk space

1) Enable CDC

Note: CDC functionality is available only for boards and Enterprise editions

 Use [AdventureWorksDW2012] GO EXEC sys.sp_cdc_enable_db GO

2) Turn on Dimaccount CDC

EXECsys.sp_cdc_enable_table@source_schema = 'dbo',@source_name   = 'Dimaccount',@role_name     = 'Myrole',@filegroup_name = 'Primary',@supports_net_changes = 1GO

At this point we can see that there are many more tables in system tables

Where cdc.dbo_dimaccount_ct is the table of record changes

Then we change a record in the Dimaccount table, and then we look at the CDC.DBO_DIMACCOUNT_CT table

We found out that he copied the entire line before the modified record and the modified record

Where the _$operation column is the order in which we operate, it can be mutilated which is the old one which is new.

CDC to prohibit Dimaccount

EXECUTE sys.sp_cdc_disable_table      @source_schema = n'dbo',     @source_name= n'  Dimaccount',    @capture_instance= N'dbo_ Dimaccount';

3.MSSQL self-bringing function change tracking

Only the modified key is recorded, and in most cases this method is good. Change records can be set for periodic deletion.

1) Turn on change_tracking function

 Use [AdventureWorksDW2012]ALTER DATABASEAdventureWorksDW2012SETChange_tracking=  on(Change_retention= 2Days, Auto_cleanup=  on);--automatic cleanup of records for the previous two daysALTER TABLE  [dbo].[Dimaccount]ENABLE change_tracking with(track_columns_updated=  on);

2) Modify the Dimaccount as above and view the effect

The change record here is not stored in the table, but is obtained through a function

 Use [AdventureWorksDW2012] SELECT *  from [dbo]. [dimaccount] 0  as T

Accountkey was recorded.

Compare with CDC

Reference documents

SYS.SP_CDC_DISABLE_TABLE (Transact-SQL)

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

SSIS: Capturing modified data