Overview
The CDC Source Plus is an SSIS Data Flow Component that retrieves incremental changes from SQL Server tables enabled for change data capture. It reads from the CDC change tables within a specific Log Sequence Number (LSN) range provided by the CDC Task Plus, delivering only the data modifications that occurred in that range. The component identifies and provides three outputs for insert, update, and delete operations, enabling efficient incremental data movement and synchronization. Depending on configuration, it can produce either all captured changes or net changes, simplifying downstream transformation and loading processes.
By integrating with the CDC Task Plus, the CDC Source Plus provides a reliable, automated mechanism for change tracking without requiring custom queries or manual change detection logic.
The CDC Source Plus supports the following processing modes for handling captured changes:
| Processing mode | Description |
|---|---|
| All | Outputs every change captured within the current CDC range, excluding any before-update values. This mode provides a straightforward stream of inserts, updates, and deletes as they were recorded. |
| All with old values | Outputs all captured changes along with their previous values. For each update operation, two records are generated — one representing the state before the update and another representing the state after the update. This mode allows comparison or auditing of changes at the column level. |
| Net | Produces a consolidated view of changes, returning a single change row per source record modified in the current CDC interval. If a row was updated multiple times, only the final outcome is emitted — for example, an insert followed by update is treated as a single update, and an update followed by delete becomes a delete. |
| Net with update mask | Similar to the Net mode, but includes additional Boolean columns named using the pattern __$ |
| Net with merge | A variation of Net mode where insert and update operations are combined into a single Merge (or Upsert) operation. This simplifies downstream logic by handling both new and modified rows through a unified output stream. |
Note: The Net processing modes require net changes to be enabled for the CDC instance on the source table.
The database and table must be CDC enabled.
Quick Start
In this section we will show you how to set up an CDC Source Plus component.
- In the SSIS Toolbox, locate the COZYROC's CDC Source Plus component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, select an existing ADO.NET connection or create a new one.
- Once the connection is selected CDC table list will get populated with CDC enabled tables only. Select CDC table.
- The Capture instance will get automatically populated when CDC table is selected.
- Select Processing mode.
- Select CDC state variable that holds the CDC state.
- Click OK to close the component editor. The component provides three outputs for insert, update and delete operations that can be attached to downstream components.
Congratulations! You have successfully configured the CDC Source Plus component.
Parameters
Configuration
Selects an existing ADO.NET SQL Server connection manager, or click button New... to create a new connection manager. The connection database must be CDC enabled.
Select the CDC source table that contains the captured changes that you want read.
Specifies CDC capture instance with the CDC table to be read. It is populated automatically once the CDC table is selected.
Select the processing mode for your needs. The available options are All, AllWithOldValues, Net, NetWithUpdateMask and NetWithMerge. Note that the net processing modes require net changes to be enabled for the CDC instance on the source table.
Select the variable containing the CDC state.
Knowledge Base
What's New
- New: Introduced component.
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.