What is CDC :-

Change data capture (CDC) is a set of software design pattern used to determine (and track) the data that has changed so that action can be taken using the changed data. Also, Change data capture (CDC) is an approach to data integration that is based on the identification, capture and delivery of the changes made to enterprise data sources.

It is a very common requirement for a data warehouse load package is to determine what has changed in the source systems and load this data into the warehouse.

All I wanted to give was a script that you can use along with adventure works Database to see what are basic stuff in CDC and how it works. off course you can use it in your Data warehouse SSIS packages.

—————————————————————

USE AdventureWorks
GO
–Enable Change Data Capture on the Database–
EXEC sp_cdc_enable_db
GO

–Enable Change Tracking on a table–
EXEC sp_cdc_enable_table
    @source_schema = ‘HumanResources’ ,@source_name = ‘Employee’,@supports_net_changes = 1,@role_name = null
go
–Simulate a Data Change —
UPDATE HumanResources.Employee
SET   ManagerID = 10 WHERE Title = ‘Production Technician – WC60’
go

select * from HumanResources.Employee where Title = ‘Production Technician – WC60’

–Get the Data —
select sys.fn_cdc_get_min_lsn(‘HumanResources_Employee’),sys.fn_cdc_get_max_lsn() 
go

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(0x00000036000017400078,0x0000003600001EC1006C,’all’)

declare @minLSN varbinary(max),@maxLsn varbinary(max)

select @minLSN = sys.fn_cdc_get_min_lsn(‘HumanResources_Employee’)
,@maxlsn=sys.fn_cdc_get_max_lsn() 

select *  from cdc.fn_cdc_get_all_changes_HumanResources_Employee
(@minLSN ,@maxLsn ,’all’)

–Disable CDC as I dont want this running on my Laptop–
EXEC sp_cdc_disable_db
GO
/*
    WHEN 1 THEN ‘delete’
    WHEN 2 THEN ‘insert’
    WHEN 4 THEN ‘update’

*/

–exec sp_configure

—————————————————————————————————–