Lookup current id in Kimball Type II dimension for fact rows

Hi all,

Maybe someone here can help me out: I have a Kimball type II dimension, where i track changes in a hierarchy. Each row has a RowStartDate and RowEndDate property to indicate from when to when a certain row should be used.

Now i want to load facts to that table. So each fact will have a certain date associated with it that i can use to lookup the right Id (a certain SourceId can have mulitiple integer Ids when there are historic changes) and then load the facts.

Is there a building block I can use for that? I could do this with SQL scripts but the client would prefer to have as much as possible done in SSIS. The Lookup transformation will only let me specify an equal (inner join where A=B) join, but i need equal for one column (SourceId) and then >= and <= (RowStart and RowEnd) to find the right row version.

Any ideas? Thanks in advance,

John

That question have been asked before. There are several works arround to that problem, here is one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=406485&SiteID=1

You may want to try a search with 'SSIS range lookup'

|||

That's it, excellent! I was googling on the wrong words.

Thanks,

John