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

Figure 11-1 shows a snippet of the SQL code in which we perform a parameterized up-
date of each row in the destination table. It is important to note that the OLE DB Com-
mand transform performs a row-by-row update against the target table. You can typic-
ally leverage this pattern as shown because most incremental operations are heavy on
new rows and have far fewer updates than inserts. However, if a particular implementa-
tion requires the processing of a very large amount of data, or if your data exploration
indicates that the number of updates is proportionally high, consider modifying this
design pattern to send those rows meant for update out to a staging table where they
can be more efficiently processed in a set-based operation.
Typical Uses
As mentioned previously, this incremental load design pattern is quite useful and ma-
ture. This pattern fits especially well when you are dealing with nonrelational source
data, or when it's not possible to stage incoming data before processing. This is often
the go-to design for incremental loads, and it fits most of this type of scenario reason-
ably well.
Make sure you keep in mind that, because we're performing the business key look-
up and the column equivalency tests within SSIS, some resource costs are associated
with getting the data into the SSIS memory space and then performing said operations
against the data. Therefore, if a particular implementation involves a very large amount
of data, and the source data is already in a SQL Server database (or could be staged
there), another design pattern such as the T-SQL MERGE operation (to be covered
shortly) might be a better option.
The following subsections describe components of the incremental load pattern and
configuration options for each.
Lookup Caching Options
When performing lookup operations, you want to consider the many options available
for managing lookup caching. Depending on the amount of data you're dealing with,
one of the following caching design patterns may help reduce performance bottlenecks.
Table Cache
A table cache for lookups is populated prior to executing a Data Flow task that requires
the lookup operation. SSIS can create and drop the table as needed using Execute SQL