locked
Lookup finds match on empty reference table RRS feed

  • Question

  • Hi all,

    In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "

    I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.

    Does anyone have an idea why? I could'nt find anything about that in BOL.

    Sébastien.

     

    Thursday, July 6, 2006 6:33 AM

Answers

  • Sebastian,

    The solution here then is quite simple. Use a Conditional Split to filter out the columns that have NULL values in the lookup columns - thereby giving you the rows that yielded a match.

    -Jamie

     

     

    Thursday, July 6, 2006 8:18 AM

All replies

  • If the lookup is returning rows, yet now rows exists, it cannot be matching, the returned columns if any will be null. You can however allow this behaviour, by ignoring errors. A failed lookup match is classed as an error, and can be set under the Configure Error Outputs button/screen. See if you have set Ignore for errors.
    Thursday, July 6, 2006 7:09 AM
  • Indeed I have configured the error to "Ignore errors" but I thought it will simply avoid the package to fail if it couldn't find any match. I don't want to use Redirect rows either as I have nothing to do with the non matching rows.
    Thursday, July 6, 2006 7:28 AM
  • Sebastian,

    The solution here then is quite simple. Use a Conditional Split to filter out the columns that have NULL values in the lookup columns - thereby giving you the rows that yielded a match.

    -Jamie

     

     

    Thursday, July 6, 2006 8:18 AM