When setting up a lookup in SSIS it is usually just a basic comparison between two key fields like an ID field. What if you want to compare and id and also find the specific date range. Let’s say the id is in the lookup reference table multiple times and you need to find the one that is has the date that surrounds a source date.
First let’s take a look at the tables.
I have a Lookup Source table that will be the source in the data flow and a Lookup Reference table that will be used in the Lookup Transform. Notice there are three IDs in the source and each id shows up twice in the reference table. So if I want to control the ID the lookup returns I will need more criteria. I want the Source Date to be between the start date and end date on the reference table. This is easy to do in the Lookup transform.
The source in the data flow is just a simple OLEDB source. I have duplicated the source date and gave both and alias. One called Source Begin Date and the other called Source End Date. This will give me the two dates I need in the lookup transform.
You must set the cache to partial cache to allow the changes to the lookup reference query.
In the column node I map over the ID. I also map the two source dates to the reference dates. If we left the lookup in this state we would get back no rows.
Go into the advanced node in the lookup transform (Not the advanced editor). Check the option to modify the SQL statement and change the comparison the start date and end date from equals to greater than or equal to, and less than or equal to as seen in the image. This will find the row that has a date range that includes the date from the source.
I placed a data viewer after the lookup in the data flow and the image shows the results. Notice I got all the rows with the term bad in the lookup info.
Now I will change all the dates in the source table. This will make the lookup find different rows.
Notice now I get the rows with good in the Lookup Info.
Keep in mind the partial cache is much slower than the full cache mode generally.
