Database Reference
In-Depth Information
changed source rows. Although the third-party components will perform well in many
cases, you should consider if they will be ideal for your scenario.
Merge Pattern
SQL Server 2008 introduced support for the T-SQL
MERGE
statement. This statement
will perform insert, update, and delete operations on a destination table based on the
results of a join with a source table. It is very efficient and provides a good alternative
for SCD processing.
Note
For more information about
MERGE
, please see the Topics Online entry “Using
MERGE in Integration Services Packages” at
ht-
There are three steps to using
MERGE
from within SSIS:
1. Stage the data in a data flow.
2. Optimize the staging table (optional).
3. Run the
MERGE
statement(s) using an Execute SQL task.
MERGE
allows a single statement to be run when it detects a row has been updated
(“matched” using the
MERGE
terminology) and when a row is new (“not matched”).
Since Type 1 and Type 2 changes require different types of processing, we'll use two
MERGE
statements to complete the SCD processing (as shown in
Figure 13-7
)
.
Search WWH ::
Custom Search