To increase the performance in SSIS for SCD

Question

Post reply

To increase the performance in SSIS for SCD

abhijitka

SSC Veteran

Points: 280
More actions
June 9, 2009 at 3:15 am

#132742

How we can increase the performance while transforming the large amount of data in SSIS using SCD. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sasidhar Chandu SSC-Addicted Points: 476 More actions · Answer 1

Hello,

Using SCD Transformation in SSIS is a performance bottleneck ,else i would suggest you to go with using Lookup, OLEDB, Union All transformation and conditional split transformations to get the required performance.

Lookup's is the only place where you are better tune the performance of the Lookup cache, you can leverage the Full Cache or Partial Cache in SSIS.

let me know if you require any assistance.

Thanks

Chandu

Tobias Kiemes SSCommitted Points: 1604 More actions · Answer 2

There is no law to do ETL in the Dataflow ;-). In my opinion there are cases where the database is stronger.

So you could save intermediate results in staging/transformation tables and then do the SCD-Work with a stored procedure. There you shouldn't process record by record but do SET-Based work. For example for a SCD2 Dimension you would need one update-Statement and one insert-Statement.

To