There are three generally accepted methods to perform the tricky range lookup in SSIS. A great blog post that outlines all three of these methods and how they perform can be found here. Per the post the best performing solution is unfortunately the one that is arguably the most difficult to set up. Fortunately for us we can get all the performance of scripting without all the work!

Scenario:

We need to perform a lookup to the segment table based on the Weekday and Time that the transaction took place. For example, assume we had a transaction take place on Tuesday at 15:30:00. This transaction would need to be mapped to the SegmentKey of 3. Screenshot of segment table below:

Now that we have identified our business requirement, let’s look at how we can use the Advanced Lookup Transform in Task Factory to solve this tricky problem.

1. For this example we will need two data flow tasks.
   1. The first data flow task will load our segment table into the TF ADV Lookup Cache Transform.
      1. Inside the first data flow task I bring in an OLEDB Source and select my Segment table.
      2. Next I bring in the TF Advanced Lookup Cache transform and connect the two transforms. (Screenshot 1)

1. Open the TF Cache transform and select “Create New Lookup Cache Transform” (Screenshot 2)

1. Click the Input Parameter tab at the top. We are going to add two parameters, one for day and one for the time of the actual transaction. In the bottom dialog box we are going to define our conditions for when a lookup should occur. For this example we have three criteria.
   1. First the day of the transaction must equal the day in the Segment table.
   2. Second the begin time must be less than or equal to the time of the transaction.
   3. Finally the end time must be greater than or equal to the time of the transaction.

1. Once the criteria have been entered click ok twice to close out of the cache connection manager.
2. Now in our second data flow we will pull in our transactional data and perform our lookup. For this example I have written a simple select statement to simulate a transaction.

   SELECT 'Tuesday' AS DayOfWeek, '15:30:00' AS TIME

3. Now that the source has been set up we need to bring in the TF Advanced Lookup Transform and open it for editing.
4. Select our Cache Connection manager from the drop down and then map our input columns to our input parameters. Screenshot below.

1. Now execute the package!

Thanks for looking.