Nonstandard SSIS lookup

Question

I have a situation where I am trying to lookup a value in one table based on values in another table, using a BETWEEN operator and not an = operator.

In one table, I have a value "EffectiveDate". I want to get a Weight number from another table, but the other table has two fields: "Inception" and "Termination". What I want to do is extract the Weight from that table for use where the EffectiveDate is between Inception and Termination.

SSIS doesn't seem to provide a way to do this. It's good at matching one column to another column, but doesn't seem to allow one to many-column comparison/operations.

Am I missing anything? Is this possible to do somehow?

Answer 1

JUst put two conditions on it mydatefield >= some date and mydatefield <= some date

Answer 2

Have a look on this URL as it could be a performance killer

http://blogs.msdn.com/mattm/archive/2008/11/25/lookup-pattern-range-lookups.aspx

A script component could be the direction forward

Answer 3

There is solution based on the third-party commercial CozyRoc SSIS+ library. CozyRoc has implemented data flow destination script, which creates memory-efficient range dictionary object. The dictionary object can then be used in CozyRoc Lookup Plus component. For more information and demonstration how to use the script, check here:

http://www.cozyroc.com/script/range-dictionary-destination

Answer 4

Take a look at:

http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range

http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/28/debunking-kimball-effective-dates.aspx

(The second link is a controversial argument for how [not] to respresent type 2 SCDs).

If you have the time and patience, writting a script component is by far the way to go from a performance perspective.