SDC Wizard Hangs on Getting Dimension Table to Load and Map Columns..

Question

SDC Wizard Hangs on Getting Dimension Table to Load and Map Columns..

good_c_ryan_19

SSC-Addicted

Points: 422
More actions
September 13, 2011 at 1:26 am

#150477

Hi i have trouble in Creating My integration package using slowly changing dimension on VS2008.
My DataFlowTask is like this:
DATASOURCE -> SLOWLYCHANGING DIMENSION -> NEW OUTPU(Insert Destination/OleDB Command)
Now,my destination Table is On datawarehouse Server which consists of Million of Records and 45 Columns.
Currently The first Integration package for that specified Table is Truncate and load which has no problem and now i need to change it into incremental load which is SCD.
While on Slowly Changing Dimension Wizard my destination tables takes more than 10 minutes to load and map into the wizard.
I Set my Business key and Slowly Changing dimension columns to Changing attribute.
When i Execute Task,it just stay on the slowly changing dimension(yellow) and didn't go through Insert Destination/OLE DB Command.
I also tried to Run this package as a job on our etl server but still It just stay running and no Records Inserted.
What is the best or other way to fix this up.
--------------------------------------------------------------------------------------------------
Thanks in Regards

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

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

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 1

Two questions. Have you tried running this out of design time? I've experienced hanging in design mode have a package run successful out of it. The second question is - Do you have a dimension with a million records? thats quite a large one.

Martin Schoombee SSCoach Points: 19127 More actions · Answer 2

good_c_ryan_19 (9/13/2011)
When i Execute Task,it just stay on the slowly changing dimension(yellow) and didn't go through Insert Destination/OLE DB Command.
I also tried to Run this package as a job on our etl server but still It just stay running and no Records Inserted.
What is the best or other way to fix this up.

You might not want to hear this, but the best way to fix it is to move away from using the SCD task. The SCD task in SSIS is convenient, but it doesn't scale well and performs very poorly if your dimension has more than a few thousand records.

You are much better off using SQL code to load the dimension...my opinion only, but you can save yourself a lot of pain by staying away from the SCD task.

Hope this helps.

Martin.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

Seconded re: moving away from the out of the box SCD. Try the free, open source version (here[/url]).

Steve.

good_c_ryan_19 SSC-Addicted Points: 422 More actions · Answer 4

Hi Sir's,

Thanks for the reply, i check the table yesterday and the destination table is replicate table from a transaction table(ERP transactions-has thousands of records) not a dimension table so that SDC is not really the best way to use. I will try to create t-sql script not an Integration Services.

Do you have other suggestion for incremental load for a transaction table?

Thanks in Regards