Creating and Syncing the Change Data Capture Transformation

A Change Data Capture Transform is a type of transformation that tracks the changes made in the source table but does not move any data. This type of transformation is designed to be used with Microsoft SQL Server Integration Services ( SSIS).

For information on how to use the data from a Change Data Capture Transformation see the section Using change data capture data.

To create a change data capture transform:

1. Click Add Transform in the Transformations page.

2. Select the source.

3. Check Create a change data capture... during the Target step.

The column mapping step will be skipped because there is no target table,

4. Select or specify a unique index if there is one in the Index tab and press Save.

After pressing Save you are returned to the Transformations page. Select the change data capture transform that was just created and press the Sync button. The Synchronize window will appear. The first time a change data capture transformation is synchronized it must create a baseline that future changes will be compared to. To do this select the CRC Baseline option. If there is no baseline and you check CRC Savepoint, the baseline will still be created. Press OK,and the transform will be synced.

The statistics for the baseline sync will show the total synced rows. Since this is a baseline, there will be no inserts, updates, or deletes.

If any adds, updates, or deletes happen to the source table (for example by using InfoNaut), you can see see those changes, by clicking the Sync button while having the same cdc transform selected.

This time select CRC Savepoint in the Synchronize window and then click OK. DataSync will now get all changes since the last time a baseline was run.

The statistics on the transform will now show the number of inserted, updated, deleted rows. Now we have the change data that can be used in SQL Server Integration Services.