Lookup range of values in SSIS

     The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns. We can also use this loopup component for performing lookup with range of values, means for suppose our source table contains all the dates and our destination table contains only range of values like startdate and enddate. Here we need to map the source table dates which are fall in the specified range, means we need to consider whatever the dates which are fall in between startdate and enddate.

          Here by using an example we can understand clearly about this feature. We are using three tables for this demo, they are student, grade, student_grade. The structure of these tables along with sample data are given below.

Table Scripts :

CREATE TABLE [dbo].[student](

[rno] [numeric](18, 0) NOT NULL,

[name] [varchar](50) NOT NULL,
[mark] [int] NOT NULL)

CREATE TABLE [dbo].[grade](

[min_mark] [int] NULL,
[max_mark] [int] NULL,
[grade] [varchar](10) NULL)

CREATE TABLE [dbo].[Student_Grade](
[rno] [numeric](18, 0) NULL,
[name] [varchar](50) NULL,
[mark] [int] NULL,
[grade] [varchar](10) NULL)

 Sample Data :

      By looking at the above tables, student table consists marks of every student and grade table consists grading pattern based on minimum and maximum marks. Now, we need to allocate grade to each student record by using lookup component, in the case of lookup we need to map columns between source table and lookup table, but here we need to check the marks fall in which set of range and assign corresponding grade to that record. Here we are using three components for this demo, the below figure will show the structure of ETL Package.

      In the OLEDB Source we need to connect student table and in the look up component we have to connect to grade table, the steps we have to perform in lookup component are as follows :

• 
  
  In the General tab select either 'Partial Cache' or 'No Cache'  (this logic won't work in full cache mode, incase if we select 'Full Cache' mode then the options in advanced tab will disabled.)
  
  
  

• 
  
  In the Connection tab, make a connection to your local server and select the table grade.
  
  
  
• In the columns tab, map the mark column to either min_mark or max_mark and select grade column from lookup table.

   

• 
  
  In the Advanced tab, check the checkbox (it will allow us to modify the SQL statement) in the custom query box and paste the following query inside the textbox.
  
  
   select * from (select * from [dbo].[grade]) [refTable]  where ? >= min_mark               and ? < max_mark
  

        

         In the above query we are cheking whether the marks >=min_mark  and marks < max_mark, only   thing we have to do here it is need to pass the correct parameter (in the place ?) by using parameter button just below the query textbox.

• 
  
  Click the parameter button, here we need to assign column mark to two parameters (we need to pass two parameters (in the place of '?') to the query
  
  
  

     Finally connect the path from lookup to OLEDB destination.In the OLEDB destination we need to select Student_Grade table. After successful execution of this package, the final output will be shown as below :