SSIS - Lookup transformation to validate fields or to get extra fields and add to the source

Today we will learn on how to use lookup within SSIS to get a field based from another table  based on a condition or to validate some of the source fields with another table 

Steps:

• Drag the Lookup task in SSIS and attach it to the source
• Double click lookup
• In general, specify Cache mode = Full cache
• Specify how to handle row with no matching entries = Redirect to no match output

• In connection, use either a table or a query to lookup
• We will use a query

• In Columns:
• Create Mappings with source and lookup table

• Select the lookup column that you want to fetch

• Drag the Lookup Match output to the appropriate destination table
• Create the mappings of source and destination
• Map source fields to target destination
• Drag the Lookup No Match output to another destination table where you want to save rows that did not match
• In the mappings of the destination, ignore the input column that you are selecting from lookup.
• This will tell us that what rows are missing lookup value

• PurchName is the field we are selecting frol lookup. So, in case of no matched rows, purchname will come as empty, so we ignore it.
• You are done
• Run and test