Expert SQL Server™ 2005 Integration Services

4.4. SSIS Slowly Changing Dimension Wizard

Now it's time to look at the built-in support for dimension ETL, called the Slowly Changing Dimension (SCD) Wizard. The SCD Wizard is a data-flow transformation and initially works like all the other transformations—simply drag and drop the transformation into the data flow and connect it to the upstream source or transformation. Figure 4-16 shows the data flow that was used earlier in the chapter with the SCD transformation now connected to the output of the Union All.

Figure 4-16. Data flow with the SCD transformation connected to the output of the Union All

Double-clicking the transformation will invoke the wizard. Like other user interface wizards, several windows will prompt you for configurations, in order to build the dimension ETL process. One of the nice advantages of the SCD Wizard is that it allows for very rapid ETL development.

The SCD Wizard supports Type 1 changing attributes, Type 2 historical attributes, inferred members, and Type 0 fixed attributes, all out-of-the-box.

When the source is connected to the SCD component, and the wizard invoked, the first screen will prompt you to identify the target dimension table, then the mapping of source columns from the data flow pipeline to the dimension columns in the dimension table, and, finally, the business keys in the source and dimension table. Figure 4-17 shows the mapping ...