We are looking at the Lookup Transformation in SSIS and we will try to clarify the Match and No Match options. The Lookup object allows you to “look” at the destination table and decide where to route records based on whether they are present or not present.
Lookup Transformation
The Lookup Transformation compares records in one (source) table and matches the records in the destination table. You can then decide to write these source records based on whether they already exist or not.
When you highlight ‘Lookup’ and drag to connect the blue arrow to a Destination or Output you will get a popup with the options for “Lookup Match Output” and “Lookup No Match Output” that looks like this:
Match: If the columns/records have a matching column/record than INSERT
No Match: If the columns/records DO NOT have a matching column/record than INSERT
Check For Duplicates Before Insert
The way I usually think of it is the “Match” means that the field(s) already exist in the destination and now I want to insert the new source records. You could use this in a scenario where you want to use SSIS to INSERT all records that are in some way different and avoid duplicates.
This might be used for:
- Logging only changes, like a server uptime status has changed
- Copying updated attributes for an existing product ID
- Tracking the status of an Order
The “No Match” is easier to understand as this means the the field or records do not exist in the destination so you want them to be INSERTed.
Neither Match or No Match will allow you to update the existing records by design but you could use a Conditional Split to accomplish this.
Match For One or Multiple Columns
Another misunderstanding is that you have to use All of the fields to involve Matching. This is the default so I can see why many think this is a requirement.
Under Columns you can “delete” the association lines and uncheck the destination fields so it will be “Matching” only specific fields you want. This box does NOT choose what columns are being transferred to the Destination.
In the image below I am removing all of the fields and limiting the match to only 2, the ProductID and the Color. The rules for “Match” and “No Match” we listed above will now only apply to these 2 fields. The other columns will be transferred to the destination but only if the 2 fields for that record get passed the Match and No Match Lookup decision.
Operation and Alias
Note: By checking the little box next to each field you can also do some changes to the data and present it as an Alias. This is outside the scope of this example but I wanted to mention this while we are looking at the Editor.
