SSIS Lookup with value range
Joe Salvatore called out for help on an interesting use of the SSIS Lookup component: Can you lookup a row using a value within a range?
Joe specified his criteria as:
- DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
- DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
- DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey
Easy! To show how it is done I've created a test database "SCRATCH" on my local machine, and created two tables and some data with the following script:
insert into dbo.sourcedata (naturalkey, modifydate) values ('a','1 Jan 2006')
create table dbo.lookupdimension (naturalkey varchar(32), roweffectivedate smalldatetime, rowexpireddate smalldatetime, surrogatekey int)
insert into dbo.lookupdimension (naturalkey, roweffectivedate, rowexpireddate, surrogatekey) values ('a', '11 dec 2005', '28 feb 2006', 1)
For an example of what we want to get in our output, run:
SELECT sourcedata.naturalkey, sourcedata.modifydate, lookupdimension.surrogatekey from sourcedata LEFT OUTER join lookupdimension on sourcedata.naturalkey = lookupdimension.naturalkey and sourcedata.modifydate >= lookupdimension.roweffectivedate and sourcedata.modifydate < lookupdimension.rowexpireddate
Create a SSIS package with a connection to the database. Add a Dataflow task, and a OLEDB
Source, Lookup, and some destination for the data (I used a Flat File Destination as it requires
the least configuration when you just want a proof of concept). Here's how my DataFlow task
Make sure the OLE DB Source connecter selects all the columns from the dbo.sourcedata
table. Hook up the OLE DB Source and Lookup.
Then open the properties of the Lookup component by double-clicking it, or right-click and
choose Properties. Select the dbo.lookupdimension to be used as lookup source.
In the Columns tab, make sure the naturalkey has a relationship between the tables. Drag
and drop the modifydate column to roweffectivedate. Select surrogatekey as
Now here is where the trick comes in. Jump to advanced, and tick Enable memory
restriction, then Modify the SQL Statement. You can now modify the select statement
that is used by the component to perform lookups. Lets set it up with the same rules that Joe
Now click the Parameters button. You should be able to set three parameters here.
naturalkey should be the input column for the first parameter, with modifydate for
both the other two.
Configure your destination, and add a Data Viewer to the flow if you want to be able to see the
Your lookup should be able to locate the row in the dbo.lookupdimension table and return the surrogatekey value.