I often get asked when we are discussing loading dimension tables in SSIS, why the key columns from the table we are looking up against don’t come through the no match output in the lookup transform for the columns that don’t match. The answer is straightforward but not always apparent. The real deal is that since they don’t match, there is no key column to return, unless you set the lookup transform to ignore the errors and pass it though as a null.

We typically want the pass through so we can check for new rows in the source and direct them accordingly in our package. See the simple example package I have below:

This package is doing a simple dimension load from Product in adventure works to DimProduct in AdventureworksDK2008. The Lookup is set as displayed:

and the metadata shows no Key Columns coming back from Dim Product in the DW.

Now if we set the Lookup to Ignore Failure in the "Specify How to Handle Rows with No Matching Entries" Option", We will see the nulls come through the matching output and we can filter them. This allows us to use the No Matching output to trap for real errors where data is present but does not align or other conditions.

As always post your questions in the BIDN Forums !