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

SSIS - split output based on a condition from source data

Today we will learn on how to use Conditional split task available within SSIS to split the out from the source data

• Drag a conditional split task in the SSIS and attach it to your source
• Open the expression editor for Conditional split 
• Create two output expressions
• Column a > 1
• Column a < 2

• Drag output 1 ( a>1) to destination 
• Drag output 2 (a <2) to destination 1
• Complete flow should look like below:

• You are done. 
• Run and test.