Lookup - No Matched Records

Hi All,

I have two tables:TableA and TableB, both of the two tables have two fields: c_IDA char(10) and c_IDB char(10); A import text file includes the ID data, the data will insert into TableB only when the ID existed in TableA
The line in the import text file like this:
00000023450000012345


in the text file: 1-10 is the c_IDA and 11-20 is the c_IDB

In the Derived Column transformation,
set the column name IDA as expression: SUBSTRING(LINE,1,10)
set the column name IDB as expression: SUBSTRING(LINE,11,10)

In the followed Lookup transfornation, I created the reference table with a sql: Select c_IDA,c_IDB from TableA, the IDA and IDB in the pipeline linked to the reference table's c_IDA and c_IDB, i then setup the error output to another log file.

The problem is even though the ID existed in the TableA, the Lookup always generate the error out put, that means the ID not been found in the TableA at all.


In the sample above, if i run the sql in the SSMS:
Select * from TableA where c_IDA = '0000002345' and c_IDB = '0000012345'

there is one record retrived.


Any idea?

TIA