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

There should be a separate process to populate the historical data for each of the
tracked CDC tables. This historical load is designed to be executed just once, and it
would load data to the destination system from as far back as is required. As shown in
Figure 11-5 , two CDC Control tasks are required. The first one (configured as shown in
the callout) is used to set the beginning boundary of the data capture. With this inform-
ation, the CDC Control task writes the CDC state to the specified state table. The
second CDC Control task marks the end point of the initial load, updating the state
table so the appropriate starting point will be used on subsequent incremental loads.
Between these two tasks sits the data flow, which facilitates the historical load of the
target table.
Figure 11-5 . CDC Control task for an initial historical load
Incremental Load
Because of the inherent differences between historical loads and incremental loads, it's
almost always preferable to create separate packages (or package groups) for each of