Dimension merge SCD stop running when data reach 9000 records

Topics: Possible Bugs/Missing Features?
Jan 6, 2012 at 8:11 AM

Hi everyone,


I have a problem with Dimension merge SCD in my SSIS package, my problem is about performance when I try to run data around 10,000 records.

So I test my package by a small group of data such 10, 100, 1,000 or 6,000 records and finished in a few seconds but when I try 9,000 records and full data(around 12,000 records) the package stop when running at 9,000 records and not doing anything even though I try to wait for it more than half a hour.

Please see screenshot of my package for more information.


Thank you for your help :)

Jan 6, 2012 at 9:03 AM
Edited Jan 6, 2012 at 9:04 AM

Couple things I've noted about the image you supplied.

1. Your existing dimension hasn't supplied any rows yet to the dimension merge (EXIST_PROM_X_ITM OLE DB Source) and this is why the package is waiting. Could you have a lock problem? Try adding WITH (NOLOCK) hint into the source query.

2. You have a 'fully blocking' component in there, a row count. Try removing this for troubleshooting purposes.

Also make sure your new data and existing dimension sources are sorted in the OLE DB source, and don't forget to tell the component which fields your sorting on. The Natural Key/Business Key is what you should be sorting on.

Looks to me like you have problems with the data sources rather than the SCD component. You can also try hooking your existing dimension source to a trash destination for troubleshooting to ensure the component is supplying data.