SSIS lookup issue - easy package, probably easy fix

Question

SSIS lookup issue - easy package, probably easy fix

justintime

Ten Centuries

Points: 1060
More actions
August 13, 2010 at 1:46 pm

#146678

i have a very simple package where i'm trying to run a lookup transform.
i have a list of department codes and BLANK division codes (flat file source)
i have a lookup on a SQL department table to try and get the division code from the table
i have a flat file destination that should end up with a list of department codes AND division codes
the package is telling me that it's not finding any match for any of the department codes. i can see in the lookup table that they are all valid values.
[Lookup [2187]] Error: Row yielded no match during lookup.
is it an issue with datatypes since it's coming from a flat file?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

theSuda SSC Eights! Points: 887 More actions · Answer 1

Did you do Ltrim(Rtrim(ColumName)) for text file source columns? Leading or trailing blanks in strings might be the problem.

_____________________________________________________________

[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Also check if there is some case sensivity.

It is a good practice to put everything in either lowercase or in uppercase, so that there can be no issues regarding the case.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

justintime Ten Centuries Points: 1060 More actions · Answer 3

how would i go about trimming from a flat file source? would that option be in the connection manager or in the flat file source transform? i looked through both and can't figure out where i should do that.

everything is lower case so i think i can rule out case sensitivity but thanks for the suggestion.

-js

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 4

You will need to use a Derive Column transformation after the Flat File source

In derived Column add a new column and In the Expression field you will need to write the expression TRIM(ColumnName) and you will need to use this Column in the lookup

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Also, trim the columns in your lookup component, you never know. I believe you can only use LTRIM and RTRIM there.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

justintime Ten Centuries Points: 1060 More actions · Answer 6

when i try to trim the lookup columns it tells me:

Connection Manager attributes have changed. The mappings of previous columns are invalid.

then when i try to get to the columns tab to map them it tells me:

Item has already been added. Key in dictionary: "Key being added: "

i even tried pumping the flat file into a temp table first so that i don't have to do any data conversions, derived columns, etc. it still fails to find my any values. i feel like it should be so simple but i can't get it to find any values. i know they are valid values because i checked that.

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 7

Do you see valid values when you preview you the data from the Flat File Source .. If yes, Then also check the data while importing by using a data viewer after the source and see if all values are coming as expected

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

If you use trim functions in the lookup component, don't forget to provide aliases for the columns!

(sorry, I forgot to mention that sooner :))

If you don't do that, every column will get the name '' (empty string) and the lookup component doesn't accept two columns with the same name. That is the dictionairy error.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

justintime Ten Centuries Points: 1060 More actions · Answer 9

i started a brand new dummy package so that i can be sure it's nothing with the data that's causing the hiccup (see attachments). i created a table called dpLookup and inserted

DP DV

1 A

2 A

3 A

4 B

5 B

6 B

7 C

8 C

9 C

10 D

i have an excel source that has a list of DP and some missing DV values

the data viewer shows valid values passing through here

i have a data conversion

another data viewer shows valid values

then a derived column

values passed are still valid

then a lookup

this is where it bombs and tells me that "Row yielded no match"

then another data conversion

then excel destination

justintime Ten Centuries Points: 1060 More actions · Answer 10

Eureka! i finally got it to work. looks like it was Rtrim only that finally made it find the lookup values.

thanks to all!

-js