Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 37

Using the CDC Components in SSIS

Lesson 36 covered loading data incrementally. That lesson covered two patterns: an insert/update pattern and a delete pattern. Those patterns also apply when using the Change Data Capture (CDC) components.

NOTE The setup of CDC within SQL Server requires sysadmin permissions. Marking a table for Change Data Capture requires db_owner permissions.

In the prior lesson, you used a modified date to determine which rows were inserted or updated, and you used a lookup from the destination to the source to find the deleted rows. Change Data Capture components will identify which rows have been inserted, updated, and deleted. Using the patterns from the previous lesson, the CDC components will replace some of the manual reads and lookups that were used to identify the changed rows. With the insert/update pattern, CDC components will replace the Read Inserted Updated Rows from SourceDB and Lookup ProductdIDs in Destination tasks. These tasks from Lesson 36 are shown enclosed in a square box in Figure 37-1.

CDC components will replace the Read rows from Destination and Lookup ProductCategoryIDs in Source task in the delete pattern from Lesson 36, as shown in Figure 37-2.

Change Data Capture is a functionality that has been available in the SQL Server Engine since the 2008 version. Change Data Capture must be set up by an administrator on the source systems prior to use of the CDC components. CDC keeps track of insert, update, and delete activity ...