question

Peter001 avatar image
Peter001 asked

SSIS customised Slow Changing Dimension

i have 50 customer and i am asked to design a SSIS package to handle the Slow Changing Dimension. i have designed the entire package for it. The real challenge for me. How can i make sure if any new customer file arrived for the first time, it will load to the table Warehouse and not in the staging table. that means **latest file** from customer should be always load into **staging table** and **oldest file** which arrived for the **first time** will be in **warehouse table.** I am pretty new to SSIS , **May anyone please share your expertise.** **SQL SERVER 2008** Thanks,
ssis-2008designslowly-changing-dimension
1 comment
10 |1200

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

Customer have their unique customerid
0 Likes 0 ·

1 Answer

·
@SQLShark avatar image
@SQLShark answered
So you should have a staging area and the dimension. Use the production key from the source system as a lookup key. Stage your data then add a look up in your SSIS to check if the production key is new. If it is loaded it. If it is not, check the other columns for changes and add a new row (depending on your SCD type). So: Load data to staging Compare staged data with data in dimension on production key New rows go to the table Existing rows - check for changes. Changes - Create a new row and close off the old row Non-changes - do nothing. Hope that makes sense.
10 |1200

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.