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
EXEC sys.sp_cdc_enable_table
@source_schema = '<schema_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
Comments
Post a Comment