title: Nonstandard SSIS lookup
body: <p>I have a situation where I am trying to lookup a value in one table based on values in another table, using a BETWEEN operator and not an = operator.</p>
  
  <p>In one table, I have a value "EffectiveDate".  I want to get a Weight number from another table, but the other table has two fields: "Inception" and "Termination".  What I want to do is extract the Weight from that table for use where the EffectiveDate is between Inception and Termination.</p>
  
  <p>SSIS doesn't seem to provide a way to do this.  It's good at matching one column to another column, but doesn't seem to allow one to many-column comparison/operations.</p>
  
  <p>Am I missing anything?  Is this possible to do somehow?</p>