Slowly Changing Dimension- Choking on large Row count

Question

Slowly Changing Dimension- Choking on large Row count

jameslauf

SSC Eights!

Points: 928
More actions
February 5, 2015 at 1:11 pm

#300859

Hello I am using SSIS 2012 SDT and I am trying to use the Slowly Changing Dimension Transformation. My problem is that I am trying to ETL about 5 million rows and it just will not complete. I am able to complete about 50 Thousand (this was the number I was using for testing before moving to production)
Does anyone have any Ideas how to get this to complete? I am not even sure how to chunk this down to run in multiple packages or jobs.
Any and all suggestions are appreciated.

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

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

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

jameslauf (2/5/2015)
Hello I am using SSIS 2012 SDT and I am trying to use the Slowly Changing Dimension Transformation. My problem is that I am trying to ETL about 5 million rows and it just will not complete. I am able to complete about 50 Thousand (this was the number I was using for testing before moving to production)
Does anyone have any Ideas how to get this to complete? I am not even sure how to chunk this down to run in multiple packages or jobs.
Any and all suggestions are appreciated.

My suggestion would be to stay as far away from that transformation task as possible. Use either t-sql or a combination of other tasks in SSIS.

Nevyn SSChampion Points: 14210 More actions · Answer 2

I haven't used that transformation/wizard before but my understanding is it is generating OLE DB Commands transformations for updating older rows.

Try changing those into destinations into a staging table, which you then use after the dataflow to do a single set based update.

OLE DB Command will send a seperate UPDATE for each row it wants to change.

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

Martin Schoombee (2/5/2015)
jameslauf (2/5/2015)
Hello I am using SSIS 2012 SDT and I am trying to use the Slowly Changing Dimension Transformation. My problem is that I am trying to ETL about 5 million rows and it just will not complete. I am able to complete about 50 Thousand (this was the number I was using for testing before moving to production)
Does anyone have any Ideas how to get this to complete? I am not even sure how to chunk this down to run in multiple packages or jobs.
Any and all suggestions are appreciated.
My suggestion would be to stay as far away from that transformation task as possible. Use either t-sql or a combination of other tasks in SSIS.

I have to agree with Martin. The SCD transformation processes everything row by row. You can edit the transformations generated by the SCD wizard but you can really only change the way the INSERT operations are done.

I personally use T-SQL Merge or INSERT/UPDATE statements that implement the equivalent logic if I need to process any serious data volume.

jameslauf SSC Eights! Points: 928 More actions · Answer 4

That is what I will do then. I need to study up on Merge and how this will work best for me. This was my first time trying this transformation and for a small amount of rows it was working great.

Thanks

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

jameslauf (2/6/2015)
That is what I will do then. I need to study up on Merge and how this will work best for me. This was my first time trying this transformation and for a small amount of rows it was working great.
Thanks

Yeah...because of the row-by-row (RBAR) nature of this task, it will not perform under high load. In my opinion, it will be much better to stage the data and use t-sql.