question

tlenzmeier avatar image
tlenzmeier asked

Slowly Changing Dimension Alternative to SSIS Wizard

Hello, I am looking for a solution for processing slowly changing dimensions (types 1 & 2) WITHOUT using the SSIS transformation wizard. The wizard peforms so poorly that I often wonder if anyone ever really uses it on large datasets. I've searched for alternatives and found several. However, I'd like to solicit opinions from the collective braintrust here as to what most folks think works best. Regards, Tom
etlssis2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sajikumar.vk avatar image
sajikumar.vk answered
I have done this, using upsert(Update/ Insert) queries... plz share further details to provide the exact query
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tlenzmeier avatar image
tlenzmeier answered
Currently, I have, as an example, a customer table. Using the SCD transformation wizard, my business keys are region and customer ID. The bulk of the remaining fields are are type 2 updates. We use row effective date, row end date, and row update date to mark the changes. The historical fields are address, city, state, customer terms, credit limit, DBA Name (our only type1 update). The primary key on the table is CustomerKey INT IDENTITY NOT NULL. The wizard gets quite bogged down as soon as it tries to digest over 20,000 rows. Non-clustered indexing helps, but it's still a dog.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tzvikl avatar image
tzvikl answered
We've usually used MERGE to implement SCD's, I know it's avoiding SSIS itself, but it gets the job done easily
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.