Compartir a través de

Lookup Transform Error: Row yielded no match during lookup

  • Artículo

Guys,
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”

clip_image001

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

Comments

  • Anonymous
    May 12, 2015
    Good

  • Anonymous
    June 10, 2015
    Thanks a lot ...

  • Anonymous
    July 15, 2015
    Very helpful.  I found that the lookup was doing case sensitive matching, and TSQL (with the collation I was using at least) was not.  I would have pulled my hair out without reviewing the error output. Thanks.

  • Anonymous
    August 25, 2015
    here i found very help full document for how to use look up. microsoft-sql-ssis.blogspot.in/.../how-to-use-look-up-in-ssis.html

  • Anonymous
    September 20, 2015
    Thnx a lot....

  • Anonymous
    April 12, 2016
    Thank you! very useful!

  • Anonymous
    March 09, 2017
    very useful

  • Anonymous
    May 05, 2017
    Worked for me, Thanks