SQL Server 2008 Enterprise includes the new Change Data Capture feature, or CDC, largely intended for use by ETL developers performing incremental loads of a data warehouse. With CDC, you can easily identify rows that have been inserted, updated, or deleted -- no more need for complex queries and other custom solutions! The other day I promised links to some resources, and now they're finally all online.
Try the 2 new end-to-end samples on Codeplex
Sandra Ward, the developer of CDC, has done extraordinary work in creating two end-to-end samples, now posted on Codeplex, that demonstrate the most common scenarios for using CDC in Integration Services. There are a few prerequisites (Database Engine obviously, Replication, AdventureWorks 2008, and so forth) but after that, the samples set everything up, and tear everything down afterwards. For each sample, there is:
- a Readme on Codeplex that describes the purpose of the sample and its components
- a white paper available for download (as a Word 2007 document) that describes the scenario, and the technology behind it, in much greater detail, and with screen shots
Scenario #1: Periodic incremental load for a specified datetime period
Scenario #2: Incremental load since the previous request, using LSN values
Read the docs, try the lab, watch the videos
In SQL Server 2008 Books Online...
Some other learning materials from Microsoft sites...