Friday, December 4, 2009

Dynamic lookup query in SSIS 2008

In SSIS 2008, lookup component has undergone a significant amount of change. Now we can parameterize the lookup query in Full Cache Mode which was not possible in SSIS 2005. Let’s see how to use dynamic lookup behavior in SSIS 2008.

Scenario:Record is a table having stats about the football players and Detail is the table having information about various players. Information is the table which is created to have consolidated information of all the players.We want to pull the data from Record and Details and push it into Information table for the players of a particular country.
This is how Detail table looks:

This is how Record table looks:


So our approach would be to take Record as Source and Detail as lookup table. Earlier we were unable to parameterize the lookup query in Full Cache mode. But in SSIS 2008, we can do that and I am going to use this enhancement while doing the lookup on Detail table.
Create 2 package scoped variables: Country and SQL (both of type string). Country is the variable used to parameterize the lookup query and SQL is the variable which is used to create the lookup query (not clear!! Well, I will explain the use of this variable when the context will come). So this is what Data Flow and the variable window look like:



Source is Record table and Lookup is using Detail table. This is how the new lookup transformation editor looks like
Click on connection and select the appropriate connection manager and write a query as:
SELECT * FROM Detail
Then click on Columns and complete the lookup editor. Then take an OLEDB destination and select the Information table as the destination and do the column mapping. So the data flow task is complete. But we have not yet parameterized our lookup query. So let’s do that (Yes, there is no parameter button in the lookup editor). Go to variable and give it a default value of Brazil. Go to SQL variable and set its “evaluate as expression” property as True. Then set an expression for this variable using Country variable as:
"Select * From Detail Where Country = '" + @[User::Country] + "'"
If you remember, I have created variable SQL to frame the lookup query. Now, we will use this SQL variable as lookup query. Select the data flow task and go to its properties. Click on the ellipsis (…) against Expressions and select the property [LookupName].[SqlCommand] as shown
Hit OK and done. Execute the package and you will see that lookup component has cached only those records for which Country is Brazil. In my Detail table there are 4 rows for Brazil so lookup has brought only 4 rows in memory instead of all the rows from Detail table. After executing the package, go to Execution Results tab to check how many rows are brought in memory by lookup component. In my package, name of lookup component is LKP_PLAYER_DETAILS and you can see that number of rows cached are 4 which is equal to number of rows for Brazil.


7 comments:

  1. Nice post Nitesh.
    Can you please give me your mailid?

    ReplyDelete
  2. Manish, e-mail id is there in my profile.

    ReplyDelete
  3. Man that is a sneaky way to get around the lookup object not having a Parameter button. That was a big help

    ReplyDelete
  4. Hi Nitesh,

    Excellent post saved me tons of time. I already searched several posts and blogs but none of them has what I am looking for. Once again thank you very much.

    ReplyDelete
  5. Thank you very much for this post! I would expect that in SSIS 2012 they would make improvements to make this option more obvious, but it didn't happen. So to change Lookup Transformation properties we have to go one level up to the DFT task properties. I would have never guessed. You really helped!

    ReplyDelete
  6. Michael....I was also expecting a more elegant way to do this but did not get any. However, as long as we get to do the things ; irrespective of the ways; it is fine.

    ReplyDelete
  7. you made my day.

    Thanks
    Ksingh

    ReplyDelete