"Row yielded no match during lookup" when using 2 columns in Lookup

I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail.

Why is this happening?

I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged:

07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL>
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\90\Shared\ErrorDumps\SQLDmpr0033.mdmp
07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No

Why am I getting this error with "Enable Memory Restriction"?

Yeah, well, also note that SSIS lookups are CaSE sensitive. Running a query in Management Studio may not be.|||I don't know why your are getting that error. Using partial cache won't fix the no-match error. By default, the lookup component treats no matches as errors; to get around taht you need to open the Lookup component and change error output to redirect-error; that way the no matches will go to the error output (red arrow)|||Your problem is probably that SQL uses different comparison rules than the SSIS Lookup does. SQL Server's default collation is case-insensitive, so it will match FooBar with FOOBAR. Lookup will see these as different strings and won't match them. Unfortunately, you can't control the Lookup behavior like you can SQL's. Best you can do is probably convert everything to upper or lower case to achieve consistency.
|||The case is exactly the same but I will try converting them all to same case all the same|||When I redirect to a flat file, ALL the records (about 900) of them get redirected. The column being looked up is a required field so I need to be able to lookup the value|||

newbie1a wrote:

When I redirect to a flat file, ALL the records (about 900) of them get redirected. The column being looked up is a required field so I need to be able to lookup the value

Watch out for trailing spaces too. Recommend you trim both the source rows and the reference rows.
|||It appears I had not trimmed everything. It works now after applying trims to the source data and the lookup. I do not need to enable memory restriction but any idea why I get "AdjustTokenPrivileges () failed (00000514)" when I do?|||Help!!! Now I am intermittently getting this "AdjustTokenPrivileges () failed (00000514)" error. What is causing this?|||

newbie1a wrote:

Help!!! Now I am intermittently getting this "AdjustTokenPrivileges () failed (00000514)" error. What is causing this?

I've never seen that error. First thing I do when a component goes goofy is to delete it and drop in a new one.