Lookup Surrogate Key from Dimension tables for EffectiveFrom and EffectiveTo Dates

There are times when we need to lookup a DimensionKey based on the ProductionKey as well as a date range. In our scenario the Dimension table has a EffectiveFrom (Date) and a EffectiveTo (Date) column, and our source has a ProductionKey and a timestamp (date).
To enable lookup between dates, first we select our Dimension table as the Reference Table and then configure the colums tab in the Lookup Transformation Editor by matching the source's timestamp with the dimension's EffectiveFrom column and the source's production key with the dimension's production key and by selecting the SurrogateKey as the Lookup Column. (see screen shot below)

Next select the Advanced tab and check both 'Enable Memory Restriction' and 'Modify the SQL statement'. Over ride the sql statement as below

select * from
select * from [dbo].[DimAsset]) as refTable
where [refTable].[AssetId] = ? and (
(? >= [refTable].[EffectiveFrom] and ? < [refTable].[EffectiveTo]) or
(? >= [refTable].[EffectiveFrom] and [refTable].[EffectiveTo] is null) )


Now click on Parameters and then map Parameter1 to the Productionkey and the rest to the source.timestamp and click Ok. Our screen should look like below.




Now our Lookup will work for between dates and even when the EffectiveTo date is NULL.
To load a dimension with effective to and effective from dates using only the modified date found in the source table read this.