kontakt os på 70 24 56 55
2800 Kgs. Lyngby
SQL Server 2012 gives us some new SSIS components that makes reading from the CDC tables created by SQL Server a lot easier.
Before this release we had to do all the house keeping our self but now almost everything is done out of the box.
There are three new components that you need to know. The first one is the CDC Source with reads
from the CDC tables and return data like any other data source component. The second one is the CDC Splitter it's basically a conditional split hard coded to split on the CDC type (__$operation). It will return three data flows containing rows for insert, delete or update. The last component is the CDC Control Task it’s the housekeeper. The CDC Control Task keep track on which rows to process.
Working with incremental loads always requires two SSIS packages. One handling the first initial load and one handling the future incremental loads.
First create a SSIS package for the initial load. Insert a CDC Control Task, a Data Flow Task and again a CDC Control Task.
Open the first CDC Control Task and set it up like this.
The result should be something like this.
Now open the last CDC Control Task and set it up like the first one except from the CDC control operation. Now select “Mark initial load end”.
The dataflow for testing purpose could be like this.
Read everything from the CDC source table and write it to a new table in the stage or data warehouse database.
The second SSIS package begins like the first one. Insert a CDC Control Task, a Data Flow Task and again a CDC Control Task.
Configure the first CDC Control Task like the other ones except for the CDC control operation. Now select “Get processing range”.
Configure the last CDC Control Task like the other ones except for the CDC control operation. Now select “Mark processing range”.
Insert a CDC Source, a CDC Splitter and three Row Counts in the Data Flow
Setup the CDC Source like this.
Connect the CDC Source and the CDC Splitter. One each of the three outputs from the CDC splitter connect one of the row counts.
The result should be like this:
To see it working run the packages like this.