SQL Server Performance

SSIS 2008 SCD Performance Issues

Discussion in 'SQL Server 2008 Integration Services' started by BhaskarDasari, May 19, 2009.

  1. BhaskarDasari New Member

    We are having 20 dimension tables and each table will be having around 20 million records.
    These tables would be loaded on a daily frequency with 5 files, each of 3 million records.
    We are currently using SCD transformation for TYPE2 load of data.( to maintain history in the dimension table.)
    But SCD is taking a long time to insert the data and below are the statistics that I recorded when I executed the package with sample files:
    Run1: File1(0.5 million records) —2 minutes (Dimension Table is empty)
    Run2: File2(0.5 million records) —13 minutes (Table has 589,000 records)
    Run3: File3(0.5 million records) —26 minutes (Table has 1,140,000 records)
    Run4: File4(0.5 million records) —37 minutes (Table has 1,680,000 records)
    Run5: File5(1 million records) —51 minutes (Table has 2,780,000 records)

    Package elapsed time : 2 hr 9 min
    1. How do i improve the performance of the SCD? If not, is there any way of loading a table parallely from file so that i can achive performance?
    2. In informatica, we have a partitioning feature to load the data parallely which greatly improves performance. Is there any equivalent feature or workaround in SSIS?
    Any help would be greatly appreciated.

  2. satya Moderator

Share This Page