sql server dts: Hi,
I've posted this question to the datawarehouse group, but thought I'd
post here as well....

I'm just getting my feet wet with SSIS, and I'm having some trouble
with the Slowly Changing Dimension transformation. I'm processing
Type 2 SCDs, and in addition to creating a new current dimension
record/setting the old dimension record to expired, I would
additionally like to write out to a table the new surrogate key that
is now current along with the old surrogate key that has just been
"expired". I cannot figure out how to modify the transformation to do
this. Any help would be greatly appreciated.

Many thanks,
Heather

Hello Heather,

So this will not be as easy as you may want because it will largely depend
on how you are going to generate your new keys.

One way might be to do something simiar to.

In the "Type 2" output you add a Multicast Transform. This pipes off the
key column/s of the changed rows. You stage these.

After the Pipeline in the control flow you can now look at these staged Business
Keys and compare them to your dimension table.

To get the new key you could ask

For Current Rows what is the Surrogate Key value for this Business Key.

To get the previous Key

Where this is not a current row in the dimension table what is the Surrogate
key for this Business Key where the EndDate is the MAX() end date for the
Business Key.




--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

[quoted text, click to view]