SSIS Merge Join not working as expected

Question

question

SamuelTHOMAS-5603 asked · Feb 19 at 01:58 PM

SSIS Merge Join not working as expected

Hello,

I'm trying to reproduce in SSIS a simple inner join:

select *
from dbo.t_journee jou
inner join t_journee_element_planning jep on jep.jep_journee_fk = jou.jou_id

So I did in SSIS:

Both source have IsSorted set to True, and the joined columns have SortKeyPosition = 1

For the 1st test, I add the where on both of the source to restrict the perimeter:
(the SQL statement returns 19 lines for this test)

This is the behaviour I was expecting, glad of this success, I decided to remove the WHERE from the left source...

But no more matching...

Did I missed something?
I'm using VS 2015.

sql-server-integration-services

image.png (22.5 KiB)

image.png (53.0 KiB)

image.png (40.1 KiB)

image.png (34.2 KiB)

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-02-23T08:58:39.56Z

SamuelTHOMAS-5603 answered · Feb 23 at 08:58 AM

I thought the SortKeyPosition = 1 was enough, I add an order by in the SQL request and I worked!

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-02-22T09:35:03.57Z

Monalv-msft answered · Feb 22 at 09:35 AM

Hi @SamuelTHOMAS-5603 ,

I decided to remove the WHERE from the left source...
But no more matching...

Could you please share the example data in the left source and the right source?

It seems that the left source does not have matching data with the right source.

Please refer to Merge Join Transformation and Using the Merge Join Transformation in SSIS packages.

Best regards
Mona

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2021-02-23T08:11:27.907Z

SamuelTHOMAS-5603 answered · Feb 23 at 08:11 AM

Thank you for yout time @Monalv-msft

In the 2nd example, without the WHERE there should be at least the 19 rows present in the 1st eample (with the WHERE), no ?
You want me to share 256.447 lines for the left source?

· Share

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question