Data Warehouse Patterns - SQL Server Integration Services Design Patterns - page 317

The MERGE statement does have a provision for auditing the various data opera-
tions. As shown in the example in Figure 11-4 , we can use the OUTPUT clause to select
out of the MERGE statement the INSERT , UPDATE , or DELETE operations. This ex-
ample shows a scenario where the data changes would be selected as a result set from
the query, which could subsequently be captured into a package variable in SSIS and
processed from there. Alternatively, you could modify the OUTPUT clause to insert
data directly into an audit table without returning a result set to SSIS.
Change Data Capture (CDC)
Along with the MERGE capability, another significant incremental load feature first sur-
faced in SQL Server 2008: change data capture (CDC). CDC is a feature of the data-
base engine that allows the collection of data changes to monitored tables.
Without jumping too far off track, here's just a bit about how CDC works. CDC is a
supply-side incremental load tool that is enabled first at the database level and then im-
plemented on a table-by-table basis via capture instances. Once CDC is enabled for a
table, the database engine uses the transaction log to track all DML operations
( INSERT s, UPDATE s, and DELETE s), logging each change to the change table for
each monitored table. The change table contains not only the fact that there was a
change to the data, but it also maintains the history of those changes. Downstream pro-
cesses can then consume just the changes (rather than the entire set of data) and process
the, INSERT s, UPDATE s, and DELETE s in any dependent systems.
CDC in Integration Services
SSIS can consume CDC data in a couple of different ways. First, using common native
SSIS components, you can access the change table to capture the data changes. You
can keeping track of which changes have been processed by SSIS by capturing and
storing the log sequence number (LSN) by using a set of system stored procedures cre-
ated when CDC is enabled.
The manual methods are still valid; however, new to SSIS in SQL Server 2012 is
an entirely new set of tools for interfacing with CDC data. Integration Services now
comes packaged with a new task and two new components that help to streamline the
processing of CDC data:
CDC Control task: This task is used for managing the metadata
around CDC loads. Using the CDC Control task, you can track the