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,
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.
18 People are following this question.
