CDC in SSIS: incremental loads without scanning the whole table

In nearly two decades working with ETL, few pains have been as recurring as the load that reprocesses the entire table every night. The package runs, re-reads millions of rows from the source, dumps everything into the destination and, in the end, you find out only a few hundred records actually changed. It's expensive for the transactional database, slow for the load window, and increasingly fragile as volume grows.

The classic answer in the SQL Server ecosystem is still valid — and still underrated: Change Data Capture (CDC) consumed inside SSIS, coordinated by the CDC Control Task. This article shows the complete pattern, with code, and honestly discusses when it still makes sense in 2026.

Changing the question

A full load asks: "what does the table look like now?". An incremental load asks: "what changed since the last time I ran?". All the savings come from swapping that question.

CDC answers the second question by reading SQL Server's transaction log. When you enable CDC on a table, SQL Server starts recording — asynchronously — every INSERT, UPDATE and DELETE into a mirrored change table (cdc.<schema>_<table>_CT). Your SSIS package no longer touches the original table to find the delta — it consumes that change record instead. Less I/O on the source, fewer rows moving, a shorter load window.

Step 1 — Enable CDC (once)

CDC is enabled on the database and then on each table you want to track. It's an administrative operation, done once:

-- 1. enable CDC on the database
EXEC sys.sp_cdc_enable_db;

-- 2. enable CDC on the source table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'Vendas',
    @role_name     = NULL,          -- no extra security role
    @supports_net_changes = 1;      -- essential for net changes

The @supports_net_changes = 1 parameter requires the table to have a primary key (or a unique index you specify) and is what enables reading net changes, explained in step 2. From here on, two SQL Server Agent jobs come into existence: capture (reads the log and populates the change tables) and cleanup (removes changes beyond the retention window). Keep that in mind — it comes back in the trade-offs section.

Step 2 — Read the net changes

CDC offers two query functions: all changes and net changes. The difference is decisive for data warehousing.

• All changes (fn_cdc_get_all_changes_...) returns every operation that happened in the window. If a sale was updated ten times overnight, you get ten rows.
• Net changes (fn_cdc_get_net_changes_...) returns the net result per key: a single row with that record's final state in the window. The ten updates become one row.

To feed dimensions and facts, net changes is almost always what you want — fewer rows, consolidated result, ready for a MERGE at the destination:

-- convert the time boundaries into LSNs
DECLARE @de  BINARY(10) = sys.fn_cdc_map_time_to_lsn(
            'smallest greater than or equal', @data_inicio);
DECLARE @ate BINARY(10) = sys.fn_cdc_map_time_to_lsn(
            'largest less than or equal', @data_fim);

-- read 1 final row per key (net)
SELECT *
FROM cdc.fn_cdc_get_net_changes_Vendas(@de, @ate, 'all');

The __$operation column indicates each row's change type (1 = delete, 2 = insert, 4 = update), which lets you route every record to the right branch of your MERGE or of your Conditional Split in the Data Flow.

Step 3 — The CDC Control Task and LSN-based state

Here's the piece that turns a clever SELECT into a reliable pipeline. The big risk in any incremental load is state: how do you know, precisely, where the previous load stopped? updated_at dates are treacherous (clocks, time zones, long transactions). CDC solves this with the LSN (Log Sequence Number) — a deterministic marker of the position in the log.

The SSIS CDC Control Task manages that state for you. The package pattern has two instances of it, surrounding the Data Flow:

1. CDC Control Task (Get Processing Range) — at the start: reads the state saved from the last run and computes the window [last processed LSN, current LSN].
2. Data Flow — in the middle: uses CDC Source + CDC Splitter to read the delta and apply inserts/updates/deletes at the destination.
3. CDC Control Task (Mark Processed Range) — at the end: writes the new processed LSN into a state table (e.g. dbo.cdc_states).

The state usually lives in a package variable persisted to that control table. The practical effect is what every data engineer wants: idempotency. Reprocessed by mistake? Run it again — the window is deterministic and you neither duplicate nor skip records. Crashed midway? The next run resumes exactly where it stopped.

The complete package pattern

Putting the pieces together, the control flow of an incremental package looks like this:

[CDC Control Task: Get Processing Range]
                |
                v
        [Data Flow Task]
   CDC Source -> CDC Splitter
        |-> Insert  -> OLE DB Destination
        |-> Update  -> Staging + MERGE
        |-> Delete  -> Staging + DELETE/soft-delete
                |
                v
[CDC Control Task: Mark Processed Range]

A good practice is not to apply UPDATE/DELETE row by row in the Data Flow. Route updates and deletes to staging tables and run a batch MERGE in the following Execute SQL Task. It's faster and transactionally cleaner than thousands of individual commands.

Why this still matters in 2026

CDC in SQL Server is a mature technology, but the problem it solves hasn't aged:

• Less I/O on the source. Reading the log instead of scanning the table reduces pressure on the transactional database — usually the most sensitive system in the estate.
• Net changes ready for the destination. The per-key consolidation fits naturally into a MERGE for dimensions and facts, reducing deduplication logic downstream.
• Deterministic LSN-based state. Idempotent, re-runnable loads are the foundation of any reliable pipeline — and the LSN delivers that without relying on fragile timestamps.

For the many teams still running SSIS on-premises, this is the native, battle-tested and cheap way to retire the full load.

When NOT to use it (the honest side)

No technique is a silver bullet. Before enabling CDC on everything:

• Log retention and space. CDC increases transaction log activity and keeps change tables for the configured retention window. Size the cleanup and the storage accordingly.
• Dependency on SQL Server Agent. The capture/cleanup jobs must be running and monitored. If the Agent stops, the delta falls behind.
• Cloud-first scenarios. If you're building something new on Microsoft Fabric or on a lakehouse, evaluate the Fabric Data Factory Copy Job (which already brings managed CDC) or the platform's native CDC before recreating the on-prem pattern. The concept is the same; the operation is simpler.
• Sources without an accessible log. CDC reads the SQL Server log. For other sources, the equivalent pattern may be tool-based CDC (Debezium, connectors) or a column watermark.

Conclusion

The question that separates an expensive load from an elegant one is simple: "what changed?" instead of "what does everything look like now?". In SSIS, CDC with the CDC Control Task answers that question natively, with deterministic LSN-based state and net changes ready for the destination. It's a twenty-year-old pattern that keeps delivering value — and that, applied well, shortens load windows, relieves the source database and makes your pipeline safe to re-run.

If you keep any large table on a full load, start there: enable CDC in a test environment, build the package with both Control Tasks and compare the load window. The numbers usually justify the change on their own.