Merge Join - HELP

Hi,

I have a SQL Statatment:

SELECT * FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X
AND A.Y= B.Y

When i execute this code in sql server returns 549 lines. I created a package with two oledb sources one for each table, sorted the tables with fields X and Y after placed a Merge Join with the fields:

A.Y join B.Y order 1

A.X join B.X order 2

both fields with the Join Key checked

But my package return 411 lines.

What's happened? :(

When a i have the code:

SELECT A.X, A.Y, B.X, B.Y
FROM TABLE1 AS A
JOIN TABLE2 AS B
ON A.X= B.X

When i did the join only one field SSIS worked fine, sql server returns 622 and SSIS returns 622 lines.

Please help-me...

Thanks,

Andr

Are your join keys all integers or does one of them have characters. SSIS is case senstive while SQL Server by default isn't. You may want to check this.|||

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

|||

Thanks Jay,

My columns are characters type and my oledb source 1 columns is lowercase and oledb source 2 columns is uppercase.

I created a Derived Column replaced my column to uppercase. :)

|||Character Map Transform might be more appropriate to change case|||

Rafael Salas wrote:

Also check that the inputs of the Merge Join are sorted...

Rafael Salas

Very good point. And note that setting IsSorted=1 on an output does NOT sort the data.

-Jamie