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-
tp://technet.microsoft.com/en-us/library/cc280522.aspx .
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