Row yielded no match during lookup.

Question

Row yielded no match during lookup.

skuthuru

SSC Veteran

Points: 222
More actions
June 24, 2008 at 9:06 am

#122109

have an Excel Source.I have to update the database table with the data from the Excel source.based on the account numbercolumn.All the Acct Nbr are in the table yet when i try to run the package it shows the [Lookup [689]] Error: Row yielded no match during lookup. what can br the error??
If i give the redirect ot ignore instead of fail component the package is working.But Scince all the records are there in the table it should go and update the table
can anyboy help me with this
thanx in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

SSIS comparisons are very data-type sensitive as well as case and accent sensitive. They are also sensitive to trailing spaces. You will often have to TRIM/RTRIM and UPPER fields to get them to match in a lookup.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

He's exactly right dude... if you have problems getting it to work even then, or you're not able to make changes you need to, then try the fuzzy operator instead. It's not case-sensitive and you have some wiggle room.

Of course, you can set the case-sensitivity if you like.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

pduplessis-723389 SSChampion Points: 10599 More actions · Answer 3

I had a very interesting one yesterday where TRIM bit me comparratively hard.

Was doing the tabledifference component for the first time (seriously rocks man), and kept on getting adverse nonsensical results (for example 9000 inserts and 8950 deletes whereas there were actually only 100 inserts and 50 deletes). Also, some updates that didnt make sense (source and destination look the same to the naked eye).

First off, after sorting the data, it cleared most of the insert and delete problem, but not all. Also, still the update counts were skew. Then I recalled what Michael said in quite a few posts regarding case sensitivity and trimming. So I revisited when I use my flatfile connection to load to the source staging table, added a derived column, where I replaced all source columns with trimmed equivalents.

TADA (panned out even my key field needed some trimming to happen as this was a varchar)

~PD

Little footnote on TableDifference, this component is free, and ohhhhh man it rocks (and no, I dont work for them and nor do I receive anything, I just know a good product when I see it)

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

I have had good luck with the TableDifference component also.

I don't know if you have noticed the "case sensitive" option, but it is really nice to be able to turn off case-sensitivity (but it slows down the component).

The source code is available also - it was really easy to open it up and add a trim to the comparison. I did so because I got tired of forgetting about the trailing spaces.