question

According to [BOL][1] >**Slowly Changing Dimension Transformation** >The Slowly Changing Dimension Wizard and the Slowly Changing Dimension transformation are general-purpose tools that meet the needs of most users. However, the data flow that the wizard generates is not optimized for performance. >Typically, the slowest components in the Slowly Changing Dimension transformation are the OLE DB Command transformations that perform UPDATEs against a single row at a time. Therefore, the most effective way to improve the performance of the Slowly Changing Dimension transformation is to replace the OLE DB Command transformations. You can replace these transformations with destination components that save all rows to be updated to a staging table. Then, you can add an Execute SQL task that performs a single set-based Transact-SQL UPDATE against all rows at the same time. >Advanced users can design a custom data flow for slowly changing dimension processing that is optimized for large dimensions. For a discussion and example of this approach, see the section, "Unique dimension scenario," in the white paper, Project REAL: Business Intelligence ETL Design Practices. You can find that white paper at Also review these Technet sources - [SQL Server 2005 Integration Services: A Strategy for Performance]( http://technet.microsoft.com/en-us/library/cc966530.aspx) - [Integration Services: Performance Tuning Techniques]( http://technet.microsoft.com/en-us/library/cc966529.aspx) [1]: http://msdn.microsoft.com/en-us/library/ms141031.aspx--

you can try to use: [SSIS Dimension Merge SCD Component (Dimension Merge SCD)][1] or [SSIS Dimension Merge SCD Component (Kimball Method SCD)][2]. On SQL Server 2008+ you can use also the MERGE statement to implement the SCD which is the fastest method for merging the dimensions. [1]: http://dimensionmergescd.codeplex.com/ [2]: http://dimensionmergescd.codeplex.com/releases/view/25249