Lookup / Merge Join / Script - How to?

Hello all,

I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:

"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _

" WHERE zip_code = @.zip_code AND addr_prim_lo <= @.street_number AND addr_prim_hi >= @.street_number " & _

" AND addr_prim_oe = @.addr_prim_oe AND street_pre = @.street_pre AND street_name = @.street_name " & _

" AND street_suff = @.street_suff AND street_post = @.street_post " & _

" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @.expiry_date)" & _

" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"

My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value

Any suggestions?

thanks for your time...

After a bit of research, I have found a few different solutions to this problem. The first uses the lookup by altering the sql statement and parameter set under the advanced tab (enable memory restriction / modify the sql statement) as discussed here http://www.julian-kuiters.id.au/article.php/ssis-lookup-with-range. The second (a bit less likeable) option was to use a script component and found here http://sqlblogcasts.com/blogs/simons/archive/2005/10/04/628.aspx

I am a bit disappointed that the range functionality was not prebuilt in to the components (other than through the memory restriction)... Does anyone else have any ideas or suggestions?

Thanks for your time.

|||Although the logic in your query is pretty clear, the context and output is not - at least not to me. What exactly do you want to have happen in your data flow as the output of this process?|||

Reading back I can see how you would be confused...

I am trying to look up information based on an address range (i.e. if you are on the 23rd block (2300 - 2400) there will be one set of police and fire municipality codes, if you are on the 24th block 2401 - 2500 there will be seperate municipality code outputs) We are aggregatting these facts based on addresses supplied from our customer base to a table provided by the state which maps out the various address blocks for which we will need to break down our earned premiums such that we can pay taxes to the firefighter and police pension plans.

I think that the main point was that there is not an intuitive way of looking up information based on a fact tables value being within the dimension tables value range. I have also had this problem looking up values based on date ranges...

|||

I don't know how much anyone is interested, but here is a topic on the feedback site which you can vote on to bring this to the ssis teams attention...

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=263594