SSIS 2008 lookup Altering the SQL statement

When setting up a lookup in SSIS it is usually just a basic comparison between two key fields like ID fields. What if you want to compare an id and also find a specific date range. Let's say the id is in the lookup reference table multiple times and you need to find the one that 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 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.

A lookup tranform is a great tool in SSIS and has many possibilities. Take some time and understand these concepts and it will be a big help when creating data flows. Let me know if you have any questions.