SSIS Multiple Lookups in one

What?
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").

Why?
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.

How?
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:
copyraw
SELECT
        [sequenceID],
        [student_reference],
        [student_title_code],
        [student_name],
        [student_gender_code],
        [student_country_birth_code],
        [student_country_domicile_code],
        [student_nationality_code],
        [student_ethnicity_code]
FROM
        [sourcedb].[dbo].[events]
  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:
copyraw
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):
copyraw
-- here we are pretending the lookup tables are prefixed with "lookup_"

SELECT
        x.[sequenceID],
        x.[student_reference],
        x.[student_title_code],
        a.[title_name] AS [student_title],
        x.[student_name],
        x.[student_gender_code],
        b.[gender_name] AS [student_gender],
        x.[student_country_birth_code],
        c.[country_name] AS [student_country_birth],
        x.[student_country_domicile_code],
        d.[country_name] AS [student_country_domicile],
        x.[student_nationality_code],
        e.[nationality_name] AS [student_nationality],
        x.[student_ethnicity_code],
        f.[ethnicity_name] AS [student_ethnicity]
FROM
        [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_" 
  2.   
  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:
copyraw
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: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used 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
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

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:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.