SQL SERVER: SSIS - Look Up Transformation Task

Today, I am going to give basic example of Lookup Transformation Task in SSIS. Lookup transformation performs lookup operation by joining data in input columns with reference table dataset columns.Lookup can be used to access addition information from the reference dataset based on the matching criteria Reference dataset can be OLEDB table, Excel file or cache file, or SQL query result. Use of Look up Transformation: In my source system (table), I have all the product with their details. Somehow I have products which belongs to the country which doesn’t exist in my reference (master) table. I assigned a job to rectify those products. I need to design ETL which gives me those records whenever we import products to our target database (table). So here, I am going to use “Lookup no match output” to capture those records by following steps: Let’s take an example to easily understand how to use Lookup Transformation in SSIS. 1. Create Source Connection: Select and drag “Data Flow Task”, from “Control Flow Items” to designer surface. Then double click it and Create a New OLEDB connection. 2. Create sample tables Now we will create tables named ‘LKP_Countries_Source’ and ‘LKP_Countries’ into Test Database from the given script. 1: CREATE TABLE [LKP_Countries_Source] 2: ( 3: [CountryCode] [int] NULL, 4: [CountryName] [varchar](100) NULL 5: ) 6: GO 7: 8: INSERT INTO [LKP_Countries_Source] 9: ( [CountryCode] 10: ,[CountryName]) 11: VALUES 12: (91, N'India') 13: ,(92, N'Pakistan') 14: ,(93, N'Afghanistan') 15: ,(94, N'Sri Lanka') 16: ,(95, N'Myanmar') 17: ,(960, N'Maldives') 18: ,(961, N'Lebanon') 19: ,(962, N'Jordan') 20: ,(963, N'Syrian Arab Republic') 21: ,(964, N'Iraq') 22: ,(965, N'Kuwait') 23: ,(966, N'Saudi Arabia') 24: ,(967, N'Yemen') 25: ,(968, N'Oman') 26: ,(971, N'United Arab Emirates') 27: ,(972, N'Israel') 28: ,(1, N'USA') 29: ,(65, N'Singapore') 30: GO 1: CREATE TABLE [LKP_Countries] 2: ( 3: [Country] [varchar](100) NULL 4: ,[Code] [int] NULL 5: ) 6: GO 7: 8: INSERT [LKP_Countries] 9: ( 10: [Country] 11: ,[Code] 12: ) 13: VALUES 14: (N'INDIA', 91) 15: ,(N'SINGAPORE', 65) 16: ,(N'USA', 1) 17: ,(N'PAKISTAN', 92) 18: GO 3. Create Lookup connection: Now you Need to select the proper OLEDB connection in “Connection Manager” tab and the source table for lookup task.    4. Columns Selection from Source Table: Select the columns to use as output columns.   5. Lookup Transformation Editor: Here, I have added “Lookup Data Transformation” Task to designer tab and click on edit to configure the Lookup transformation. 6. Handle No Match output: Now we need to configure various sections in the “Lookup Transformation Editor”. In General section, select “Redirect rows to no match output” to handle the unmatched data from the lookup task.  Here we will need to select Cache mode as “Full Cache”. This option is used to improve the performance while handling large scale of data. Keep connection type as “OLEDB Connection Manager”, as we are using OLEDB source. When you use Cache File as data source then you will need to select “Cache Connection Manager” Last option provide various ways in which not matched data can be handled. Ignore failure – ignores the failure and executes the next task. Redirect rows to error output – moves the not matched rows to red output to handle them separately. Fail component – throws an exception and stops processing further tasks. Redirect rows to no match output – switches rows to the secondary output, and user can handle it differently to matching data 7. Set Connection Manager for Lookup table: In Connection section select the reference table with proper connection. This list will get compared with source dataset for matching the data. 8. Column mapping for Lookup table: In Columns section, select the available input columns and map it with the available lookup columns. This will create a join between 2 source datasets.We have used “Full Cache Mode”, so Advanced section will be disabled and in “Error Output” keep fields as it is. 9. Input Output Selection Setup: Select new “OLEDB Destination” transformation and drag it to the designer surface. Drag green arrow from “Lookup Transformation” Task to “OLEDB Destination” and provide “Lookup Match Output” as Output and click OK. 10. Create Output table for Match and Not Matched Data: Now we will need to create output tables to store the matched as well as not matched result. 1: CREATE TABLE [LKP_Output_Match] 2: ( 3: [CountryCode] INT, 4: [CountryName] VARCHAR(100), 5: [Country_Calling_Code] INT 6: ) 7: GO 8: 9: CREATE TABLE [LKP_Output_NO_Match] 10: ( 11: [CountryCode] INT NULL, 12: [CountryName] VARCHAR(100) NULL, 13: ) 14: GO 11. Data Mapping for Result table: Now I need to provide mapping for the Output Table to store the Matched Data. 12. Complete Data Flow for Lookup Transformation: In order to handle the “Not Matched data”, provide the link of Not Matched data to OLEDB Destination table “LKP_Output_No_Match”. It will store the not matched results.   13. Package Execution and Result: Now let’s execute the Package and check with the output inside the tables we have created to store the result as in “LKP_Output_Match” table for Matched Data and “LKP_Output_NO_Match” table for Not Matched Data. 1: SELECT * FROM LKP_Output_Match 2: 3: SELECT * FROM LKP_Output_NO_Match 4: GO Result :  Lookup Transformation can be used in various ways according to the requirements and can be implemented accordingly. This was just an understanding document for Lookup Transformation. Reference: Tejas Shah (www.SQLYoga.com)