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

Each of these tools is effective when used properly, though some are better suited
than others for different scenarios. We'll now step through the design patterns with
each of these.
Native SSIS Components
The first incremental load pattern we'll explore is that of using native components
within SSIS to perform the load. Through the use of lookups, conditional splits, and
OLE DB command components, we can create a simple data path through which we
can processes new and changed data from our source system(s).
Employing this design pattern is one of the most common ways to perform an in-
cremental load using SSIS. Because all of the components used in this pattern have
been around since SSIS was introduced in 2005, it's a very mature and time-tested
methodology. Of all of the incremental methodologies we'll explore, this is certainly
the most flexible. When properly configured, it can perform reasonably well. This is
also the design pattern with the fewest external dependencies; almost any data can be
used as a source, it does not require database engine features such as CDC to be en-
abled, and it does not require any third-party components to be installed.
The Moving Parts
When you are using this design pattern, the most common operations you will perform
will include the following steps:
1. Extract data from the data source(s). If more than one source is used,
they can be brought together using the appropriate junction compon-
ent (Merge, Merge Join, or Union All transforms).
2. Using the Lookup transform, join the source data with the target table
based on the business key(s).
3. Route changed rows to the target table. Unmatched rows from the
step 2 can then be routed directly to the target table using the appro-
priate database destination component.
4. For the source rows that have a business key match in the target table,
compare the other values that may change. If any of those source val-
ues differs from the value found for that row in the destination table,