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

those rows in the destination table will be updated with the new val-
ues.
Figure 11-1 shows a typical data flow design for these operations. In the first cal-
lout, you can see that we need to set the option on the Lookup transform to Redirect
Rows to No Match Output. When you use this setting, any source rows that are not re-
solved to an existing row in the destination table are sent down an alternate path—in
this case, directly to the destination table.
Figure 11-1 . Incremental load using atomic SSIS components
Next, we'll apply the Conditional Split transform to the matched rows. As shown in
the snippet on the lower left of Figure 11-1 , we'll use a bit of the SSIS expression lan-
guage to compare equivalent columns between source and destination. Any rows with
an exact match will not go through any further processing (though you could send them
to a Row Count transform if you want to capture the count of source rows with no ac-
tion taken).
Finally, the rows that matched the business key but not the subsequent attribute val-
ues will be sent to the OLE DB Command transform. The code on the lower right in