Database Reference
In-Depth Information
start and end points of the initial (historical) load, as well as retrieve
and store the processing range for an incremental load.
CDC Source:
The CDC Source is used to retrieve data from the CDC
change table. It receives the CDC state information from the CDC
Control task by way of an SSIS variable, and it will selectively re-
trieve the changed data using that marker.
CDC Splitter:
The CDC Splitter is a transform that will branch the
changed data out into its various operations. Effectively a specialized
Conditional Split transform, it will use the CDC information received
from the CDC Source and send the rows to the Insert, Update, Delete,
or Error path accordingly.
For the purposes of reviewing CDC capabilities as part of an SSIS incremental load
strategy, we'll stick with the new task and components present in SSIS 2012. In sys-
tems using SQL Server 2008, know that you can meet the same objectives by employ-
ing the manual extraction and LSN tracking briefly described previously.
Change Detection in General
Detecting changes in data is a subscience of data integration. Much has been written on
the topic from sources too numerous to list. Although CDC provides handy change de-
tection in SQL Server, it was possible (and necessary!) to achieve change detection be-
fore the advent of CDC. It is important to note that CDC is not available in all editions
of SQL Server; it is also not available in other relational database engines.
Checksum-Based Detection
One early pattern for change detection was using the Transact-SQL Checksum func-
tion. Checksum accepts a string as an argument and generates a numeric hash value.
But Checksum performance has proven less than ideal, generating the same number for
different string values. Steve Jones blogged about this behavior in a post entitled “SQL
Server Encryption—Hashing Collisions” (
www.sqlservercentral.com/
hashing-collisions/
)
. Michael Coles provided rich evidence to support Steve's
claims in the post's comments (
www.sqlservercentral.com/blogs/
Search WWH ::
Custom Search