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:
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
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:
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]
- 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]
copyraw
The one lookup task:
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
- 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
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
- -- 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:
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
- 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
Category: SQL Server Integration Services :: Article: 573
