Recently, I had to implement a lot of data warehousing solutions, hence I stumbled on an error in the SSIS Lookup transform task which I wanted to share. Most of my package tasks went on fine till it hit the below error in the Lookup task after matching over a million rows.
I then realized the default behavior of a lookup is to fail when there is a no-match. That is happening because I was using the default error configuration of the Lookup task "Fail Component"; it would fail if a no match occurs. I needed to change that to 'Redirect row' and then use the error output of the task to send those rows to where ever I wanted. So, when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error I originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based on requirements, one can decide what to do with those errors. E.g. for a data warehouse you may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to a custom error table. Below are the steps I took:
--We changed the Lookup Task property to “Redirect Rows to Error Output”
After configuring the error output, I created Flat File destination, redirected error to the flat file.
Now the lookup completes without errors. It may still fail with the same error for other lookup tasks (if any) in the package; we need to repeat the above steps for each of the Lookup tasks. If there were any "bad rows" i.e. rows not matching Lookup Criteria, they will be dumped in the flat file configured above.
So we would be able to figure out the "bad data" this way. It is also worth mentioning that SSIS Lookup transforms are case sensitive i.e. there will be a no-match condition even if there is a difference in the case and that was exactly what happened with me.
Happy Looking up! J
Author : Debarchan(MSFT) SQL Developer Engineer, Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead , Microsoft