locked
SSIS Lookup Error ......Row yielded no match during lookup. RRS feed

  • Question

  • I have a lookup which I know should bring back data but it is failing.

    I get the following error:

    [Lookup- Spec [1257]] Error: Row yielded no match during lookup.
    Mr Shaw
    Thursday, January 22, 2009 5:31 PM

Answers

  • Common problems with lookups failing are case-sensitivity or space-sensitivity (your flow has trailing spaces, but your lookup table doesn't, or vice versa).

    Perhaps one of those is plaguing you too.

    To get the root cause, run SQL Profiler on the database that's being "looked up", and you'll get to see the exact query SSIS posts to the database to do the lookup.  Copy it and paste into SSMS to debug - I'm sure you'll find the problem in no time that way if it's not one of the common problems...
    Todd McDermid's Blog
    • Proposed as answer by Todd McDermid Thursday, January 22, 2009 7:16 PM
    • Marked as answer by Tony Tang_YJ Thursday, January 29, 2009 9:54 AM
    Thursday, January 22, 2009 5:44 PM

All replies

  • Common problems with lookups failing are case-sensitivity or space-sensitivity (your flow has trailing spaces, but your lookup table doesn't, or vice versa).

    Perhaps one of those is plaguing you too.

    To get the root cause, run SQL Profiler on the database that's being "looked up", and you'll get to see the exact query SSIS posts to the database to do the lookup.  Copy it and paste into SSMS to debug - I'm sure you'll find the problem in no time that way if it's not one of the common problems...
    Todd McDermid's Blog
    • Proposed as answer by Todd McDermid Thursday, January 22, 2009 7:16 PM
    • Marked as answer by Tony Tang_YJ Thursday, January 29, 2009 9:54 AM
    Thursday, January 22, 2009 5:44 PM
  • Ok. I will try that in the morning. thanks..
    Mr Shaw
    Thursday, January 22, 2009 6:42 PM
  • Be Simple..

    -Double click in Lookup

    -In General, in dropdrown option name "Specify how to handle rows with no matchin entries"

    Select option "Redirect rows to no match output" (the error disappear)

    -Finally use the data viewers for identify errors.

    I hope to be useful.

    Thursday, January 15, 2015 2:14 AM
  • Whats the caching mode used? Is it full or partial cache?

    There's a difference in the way comparison is done in both mode as in one case comparison takes place in SSIS engine whereas in other case it takes place in SQLServer.

    See this

    http://visakhm.blogspot.in/2014/05/string-comparison-in-ssis-lookup-task.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, January 15, 2015 3:19 AM
  • Correct answer, thanks
    Friday, February 16, 2018 1:27 AM