SSIS Lookup Transformation No Match Output Only Populates Null

Question

I am trying to use the lookup transformation but can not seem to get the functionality out of it that I need. I have two tables that are the exact same structure

Temp Table (input): Smaller table but may have entries that do not exist in other table Reference Lookup Table: Larger table that may not have identical entries to Temp Table.

I am trying to compare the entries of the Temp Table to the entries of the Reference Lookup Table. Anything that exists in the Temp Table, but not the Lookup should be output to a separate table (No match output).

It is a very simple Data Flow, but it does not seem to accomplish the lookup properly. It will find "No Match" rows, but the "no match" table is populated with null values for every column. I am trying to figure out why the data is losing its values?

How the Lookup is setup:

Answer 1

The data in temp table is what drives your data flow. 151 rows flowed out of it.

Your lookup is going to match based on whatever criteria you specify and you've identified that if there is no match, I want to push the no-match data into a table.

Since the lookup task cannot add columns to the no-match output path, this would imply your source (temp table) started NULL across the board.

Drop a data viewer/data tap onto the data flow between the lookup and the destination and then compare that data to your source. I suspect you're going to discover that the process that populated Temp table is at fault.

In the Lookup Transformation, in the columns tab you have identified that you want to use the value from the reference table to replace the value from the source.

Which works great until you get a no-match. In which case, the component is going to do the non-intuitive (even to me with 15+ years of working with it) action of update that column whether it matches or not.

Source query

SELECT 21 AS tipID,  NULL AS tipYear
UNION ALL SELECT 22, 2020
UNION ALL SELECT 64263810, 2020

This adds three rows to my data flow, the first with no tipYear and the next two rows with a year of 2020. Stamp of 1 in the below image

Lookup query

SELECT 
*
FROM
(
    values (20, 1111), (21, 2021), (22, 2022)
)D(tipID, tipYear)

This reference data will supply a year for all the matches (21 and 22). In the matched path, we'll see 21 supplied with a value and 22 will have its year updated. Stamp 2 in the image

For id 64263810 however, no match will be found and we'll see the initial value of 2020 replaced with the matching row aka NULL. Stamp 3

Lessons learned. If you need to use the data from the reference table but have a no-match output path, do not replace column in the lookup transformation (unless your intention is to wipe out data)