Change Data Capture Tables

A Change Data Capture (do not confuse it with Change Tracking) is a solution for datawarehousing applications when you want to perform incremental data loads on DW databases.
This info is stored in special system tables created stored in each database where this option is set.

This option is set at database level and you must use these stored procedures to set this property on the affected database:

EXEC sys.sp_cdc_enable_db --Enable CDC

EXEC sys.sp_cdc_disable_db --Disable CDC

Then you enable it for an specific table using the following stored procedure, using the standard schema.table convention:

EXEC sys.sp_cdc_enable_table

       @source_schema = '<schema_name>'

       ,@source_name = '<table_name>'

       ,@role_name = '<role>'

For simple applications, these options are fairly enough. Once enabled you can use SSDT to handle the data to suit your needs, using the CDC Source and CDC Split Transformation inside a Data Flow task:

"Sample" usage

CDC Source validates that the table you select is CDC enabled, while CDC Splitter splits each operation based on the __$operation Column on the table.


References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/change-data-capture-tables-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-flow-components