Complex SSIS Lookup/Merge Join Using NK and Dates

I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:

Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey

-- Brian

Hi Brian. I would go ahead and join based on your NaturalKey, then use a secondary step such as a Conditional Split to filter based on the dates.

Jay|||Brian, I just came across the same problem. Any chance you can post the code example to accomplish this? thanks|||

There's a possible option here: http://blogs.conchango.com/jamiethomson/archive/2006/03/29/3294.aspx

Beware though, no data gets cached in memory.

-Jamie