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

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

Memory used

I've got this problem: I execute a bulk insert via a
stored procedure of about 500000 records. The bulk insert
works fine, but the memory used grows up very fast. Then,
it never goes down.
So if I try to do another bulk insert via the same stored
procedure, the sp doesn't work and the statement have been
terminated without any error.
The server is a pentium 4 (single processor) 1,5 Ghz and 1
Gb of memory, with windows 2000 server service pack 3 and
it is used for development purposes.
Thank you.marco
Are you sure there are no other activites on the server at the same time?
Have you looked at Profiler ?
"marco" <angilerm@.lloydadriatico.it> wrote in message
news:21e701c3a925$4cad3650$a601280a@.phx.gbl...
> I've got this problem: I execute a bulk insert via a
> stored procedure of about 500000 records. The bulk insert
> works fine, but the memory used grows up very fast. Then,
> it never goes down.
> So if I try to do another bulk insert via the same stored
> procedure, the sp doesn't work and the statement have been
> terminated without any error.
> The server is a pentium 4 (single processor) 1,5 Ghz and 1
> Gb of memory, with windows 2000 server service pack 3 and
> it is used for development purposes.
> Thank you.sql