SQL Server Performance Forum – Threads Archive

SSIS package with Slowly Changing Dimension

I am new to the SQL Server 2005 Integration Services. We are building our first Data Watehouse and one of the ETL requirement is to use SSIS. Can anyone help me on how to put together the use of Lookup, Slowly changing Dimension, and Union All, in building my package. I have tried several times but I’m not getting a result. The objective is simple: 1. populate the dimension table (which is initially empty) 2. check the dimension if the business key/natural key already exist 3. if not create the record, if it does, apply historical SCD type 2 change. thanks for your help.

---

Hi,
By looking at your post, it seems you are creating a dimension table in sql server for the purpose of linking it to OLAP dim.
In this case i’m not sure why you have done that, as in 2005 Analysis services also allows you to create named queries directly in source view of cube. Anyways, in SSIS package, you need two steps
1>SQL task
2>AS processing task 1>In sql task you need to fire sql statements to build you dim table.
Checking of records will be also part of this task.
2>In Dimension processing task you can mention the dimension to be processed as process update which will keep your cube online and all the chnages will get updated also process update will take care of new members.
This can be performed either by firing XML or directly mapping the dim to process.

---

]]>