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:
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]
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
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_"

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

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

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.