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.
No comments:
Post a Comment