SSIS Merge Join not working as expected

Question

question

SamuelTHOMAS-5603 asked Feb 19, '21 Monalv-msft edited Apr 7, '21

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)

5 |1600 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, '21

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

5 |1600 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, '21 Monalv-msft edited Apr 7, '21

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.

5 |1600 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, '21

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?

5 |1600 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