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

is used during the load window, dropping keys and indexes is not an option to improve
load performance. All related data must become available in the data warehouse at
roughly the same time to maintain source-transactional consistency. By nature, the data
does not lend itself to partitioning. What to do?
Consider load staging , where all the data required to represent a source transaction
is loaded into stage tables on the destination. Once these tables are populated, you can
use Execute SQL tasks to insert the staged rows into the data warehouse destination
table. If timed properly, you may be able to use a bulk insert to accomplish the load.
Often, data loads between tables in the same SQL Server instance can be accomplished
more efficiently using T-SQL rather than the buffered SSIS Data Flow task. How can
you tell which will perform better? Test it!
The Slowly Changing Dimension Wizard
The Slowly Changing Dimension (SCD) Wizard is another veteran of the SSIS incre-
mental load arsenal. As its name implies, it is designed specifically for managing SCD
elements in a data warehouse. However, its use is certainly not limited to dimension
processing.
The SCD Wizard has been a part of SSIS ever since the product's introduction in
2005. At first glance, it is the natural choice for handling slowly changing dimension
data in SSIS. This tool is built right into Integration Services, and it is designed spe-
cifically for the purpose of SCD processing.
The SCD Wizard is surfaced as a transformation in SSIS and is leveraged by con-
necting the output of a data source (the incoming data) to the input of the SCD trans-
formation. Editing the SCD transformation will kick off the wizard, as shown in Figure
11-2 .