Hi Naveen,

According to your description, you want to implement SCD Type 6 in SSIS without using SCD Transformation.

Based on my research, we always use OLE DB Command combine with Lookup Transformation to simulate the SCD Transformation in SSIS. Besides, Slowly Changing Dimension Transformation will cause a performance bottleneck for large tables. 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. We can replace these transformations with destination components that save all rows to be updated to a staging table. Then, we can add an Execute SQL task that performs a single set-based Transact-SQL UPDATE against all rows at the same time.

Reference: SSIS Tips: Implementing SCD Functionality Using Hashing Method Design Tip #152 Slowly Changing Dimension Types 0, 4, 5, 6 and 7

Thanks, Katherine Xiong

Katherine Xiong TechNet Community Support