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

Are you casting the Derived Column to a DT_STR to match your char(10), or is it still set to DT_WSTR?

|||

I am using DT_STR

Thanks

|||

Have you used a data viewer immediately before the Lookup to validate that the values are what you expect?

Another potential issue is the collation settings on the database. Do you know what they are?

|||

Thanks again.

I put the data viewer before the Lookup and the data are displayed correctly. I am using the digit to present string, is it related with collation?

|||Collation affects sorting. It's one of the setting made when the SQL Server is set up, and under 2005, it can be configured for each database individually. It affects string comparisons, in some cases.

|||

But why I am able to retrive the correct records via Select in SSMS?

jwelch wrote:

Collation affects sorting. It's one of the setting made when the SQL Server is set up, and under 2005, it can be configured for each database individually. It affects string comparisons, in some cases.

|||

Because there the entire string is being interpeted by the database engine, versus in SSIS where some data is coming in from a flat file, and the other data is retrieved from the database engine.

That being said, I don't know for certain that this is your problem, but it is something to check.

|||

Whe i use Select SERVERPROPERTY(N'Collation'), it returns: Latin1_General_CI_AS

That means:

Case-insensitive, accent-sensitive, kana-insensitive, width-insensitive

is that?

jwelch wrote:

Because there the entire string is being interpeted by the database engine, versus in SSIS where some data is coming in from a flat file, and the other data is retrieved from the database engine.

That being said, I don't know for certain that this is your problem, but it is something to check.

|||

That one is unlikely to be causing this problem.