SSIS Multiple Lookups in one

A quick article on an alternative to multiple lookups in SSIS. For any SSIS developer trying to create packages that need to decode a number of values into their full name/description (eg. "M" to "Male", "Prof" to "Professor").

Our extract phase in the SSIS package takes data from a source database and puts it in staging in the exact form it came through. Our transform package will convert the data and output it to the target system. For our package to decode what "M" means, it needs to look this up in a table on the original server. We had around 12 lookups to do, so you could do this:
But we didn't want to do this.

As a proof of concept, I created one lookup task that executes a SQL query which returns all the joined tables and displays the decode name. Let's pretend the info we want is rather simple:
  1.  SELECT 
  2.          [sequenceID], 
  3.          [student_reference], 
  4.          [student_title_code], 
  5.          [student_name], 
  6.          [student_gender_code], 
  7.          [student_country_birth_code], 
  8.          [student_country_domicile_code], 
  9.          [student_nationality_code], 
  10.          [student_ethnicity_code] 
  11.  FROM 
  12.          [sourcedb].[dbo].[events] 
Simple bit of SQL but as you can see we have a few fields that need decoding (suffixed by "_code"). Run as it is, yields something like the following:
sequenceID      student_reference       student_title_code      student_name            student_gender_code     student_country_birth_code      student_country_birth_code      student_nationality_code        student_ethnicity_code
--------------- ----------------------- ----------------------- ----------------------- ----------------------- ------------------------------- ------------------------------- ------------------------------- --------------------------
0000000001      1234567                 MR                      Joe Lipman              M                       726                             5826                            000                             39
0000000002      0987654                 MAJ                     Big Bird                M                       771                             771                             771                             15
  1.  sequenceID      student_reference       student_title_code      student_name            student_gender_code     student_country_birth_code      student_country_birth_code      student_nationality_code        student_ethnicity_code 
  2.  --------------- ----------------------- ----------------------- ----------------------- ----------------------- ------------------------------- ------------------------------- ------------------------------- -------------------------- 
  3.  0000000001      1234567                 MR                      Joe Lipman              M                       726                             5826                            000                             39 
  4.  0000000002      0987654                 MAJ                     Big Bird                M                       771                             771                             771                             15 
The one lookup task:

Changing the lookup to "use results of an SQL query":

Which would code with the joined tables as follows (note that in our case we needed the lookups on the source database which was not the same as our staging one which this package gets as its source):
-- here we are pretending the lookup tables are prefixed with "lookup_"

        a.[title_name] AS [student_title],
        b.[gender_name] AS [student_gender],
        c.[country_name] AS [student_country_birth],
        d.[country_name] AS [student_country_domicile],
        e.[nationality_name] AS [student_nationality],
        f.[ethnicity_name] AS [student_ethnicity]
        [sourcedb].[dbo].[events] x
        LEFT JOIN [sourcedb].[dbo].[lookup_titles] a ON x.student_title_code=a.lookup_title_code
        LEFT JOIN [sourcedb].[dbo].[lookup_genders] b ON x.student_gender_code=b.lookup_gender_code
        LEFT JOIN [sourcedb].[dbo].[lookup_countries] c ON x.student_country_birth_code=c.lookup_country_birth_code
        LEFT JOIN [sourcedb].[dbo].[lookup_countries] d ON x.student_country_domicile_code=d.lookup_country_domicile_code
        LEFT JOIN [sourcedb].[dbo].[lookup_nationalities] e ON x.student_nationality_code=e.lookup_nationality_code
        LEFT JOIN [sourcedb].[dbo].[lookup_ethnicities] f ON x.student_ethnicity_code=f.lookup_ethnicity_code
  1.  -- here we are pretending the lookup tables are prefixed with "lookup_" 
  3.  SELECT 
  4.          x.[sequenceID], 
  5.          x.[student_reference], 
  6.          x.[student_title_code], 
  7.          a.[title_name] AS [student_title], 
  8.          x.[student_name], 
  9.          x.[student_gender_code], 
  10.          b.[gender_name] AS [student_gender], 
  11.          x.[student_country_birth_code], 
  12.          c.[country_name] AS [student_country_birth], 
  13.          x.[student_country_domicile_code], 
  14.          d.[country_name] AS [student_country_domicile], 
  15.          x.[student_nationality_code], 
  16.          e.[nationality_name] AS [student_nationality], 
  17.          x.[student_ethnicity_code], 
  18.          f.[ethnicity_name] AS [student_ethnicity] 
  19.  FROM 
  20.          [sourcedb].[dbo].[events] x 
  21.          LEFT JOIN [sourcedb].[dbo].[lookup_titles] a ON x.student_title_code=a.lookup_title_code 
  22.          LEFT JOIN [sourcedb].[dbo].[lookup_genders] b ON x.student_gender_code=b.lookup_gender_code 
  23.          LEFT JOIN [sourcedb].[dbo].[lookup_countries] c ON x.student_country_birth_code=c.lookup_country_birth_code 
  24.          LEFT JOIN [sourcedb].[dbo].[lookup_countries] d ON x.student_country_domicile_code=d.lookup_country_domicile_code 
  25.          LEFT JOIN [sourcedb].[dbo].[lookup_nationalities] e ON x.student_nationality_code=e.lookup_nationality_code 
  26.          LEFT JOIN [sourcedb].[dbo].[lookup_ethnicities] f ON x.student_ethnicity_code=f.lookup_ethnicity_code 

Note this also allows NULLs so, our sequence identifier and student reference are never blank so it is safe to to use "Lookup Match Output" as rows with neither sequence nor student reference will not come through the system. Lookups that failed will return as NULL. Selecting the fields to output into the data-flow:
sequenceID      student_reference       student_title_code      student_title           student_name            student_gender_code     student_gender  student_country_birth_code      student_country_birth           student_country_domicile_code   student_country_domicile        student_nationality_code        student_nationality             student_ethnicity_code          student_ethnicity
--------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- --------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- 
0000000001      1234567                 MR                      Mr                      Joe Lipman              M                       Male            5826                            Philippines                     000                             England                         39                              British National                39                              Other Asian Background
0000000002      0987654                 MAJ                     Major                   Big Bird                M                       Male            771                             United States of America        771                             United States of America        15                              United States Citizen           15                              Gypsy or Traveller
  1.  sequenceID      student_reference       student_title_code      student_title           student_name            student_gender_code     student_gender  student_country_birth_code      student_country_birth           student_country_domicile_code   student_country_domicile        student_nationality_code        student_nationality             student_ethnicity_code          student_ethnicity 
  2.  --------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- --------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- ------------------------------- 
  3.  0000000001      1234567                 MR                      Mr                      Joe Lipman              M                       Male            5826                            Philippines                     000                             England                         39                              British National                39                              Other Asian Background 
  4.  0000000002      0987654                 MAJ                     Major                   Big Bird                M                       Male            771                             United States of America        771                             United States of America        15                              United States Citizen           15                              Gypsy or Traveller 
Category: SQL Server Integration Services :: Article: 573

Credit where Credit is Due:

Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman

Related Articles

Joes Revolver Map


Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Donate to Joel Lipman via PayPal

Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.