locked
Row yielded no match during lookup RRS feed

  • Question

  • In SSIS. I am having trouble exporting records
    that don't match from a lookup transformation. I get the following
    error:

    Row yielded no match during lookup.


    I would really like to have a list of all records that did not match so
    that I could send an email of those missing rows

    Please give me solution with example

    Thanks

    Friday, September 8, 2006 6:18 AM

Answers

  • That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

     

    Rafael Salas

     

    Friday, September 8, 2006 3:36 PM

All replies

  • That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

     

    Rafael Salas

     

    Friday, September 8, 2006 3:36 PM
  • Thanks for email

    I m new in SSIS, please suggest such error output with example.

     

    Monday, September 11, 2006 6:36 AM
  •  leo1 wrote:

    Thanks for email

    I m new in SSIS, please suggest such error output with example.

     

    Leo,

    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 you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your 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 an custom error table.

    Rafael Salas

    Monday, September 11, 2006 12:26 PM
  • thanks for reply.

    I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

     

    Can you please suggest me (Steps) or example how to do this.

    thanks

    Monday, September 11, 2006 1:16 PM
  • Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

     

    -jamie

     

    Monday, September 11, 2006 1:18 PM
  • I have got all such rows in the file using the flat file destination in data flow

    Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

    Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

    Kindly suggest possibly by example or steps.

    Tuesday, September 12, 2006 5:40 AM
  • I use a lookup often for different purposes.

    For example currently i'm using it to pull "Open House" information for Properties.
    Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.
    Monday, January 8, 2007 9:01 PM
  • I am doing the data migration from a data base to a data warehouse, the same error occoured for the lookup transformation, may I ask what is the best way to handle this error? Is to output the error to a flat file destination or  replace the nulls by  default values and insert them to the destination table?

    Wednesday, November 5, 2008 1:09 AM
  • I'm testing with SSIS (SQL2014) training lesson, this happened in my 1st lesson, finally, I found this is really:

    "no match data during lookup"

    Because example txt data is year 2001-2004, example SQL DWH data is year 2005-2008

    answer is, program is perfect, the data source is not match.

    another problem in txt source in CurrencyDate, it need DT_DBdate type, but after change DS define, refresh the dataflow control item is needed, just open the control, and click ok

    Monday, May 8, 2017 1:22 AM