SSIS Merge Join not working as expected

Question

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.

Answer 1

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

Answer 2

Hi @Samuel THOMAS ,

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.

Answer 3

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?

Answer 4

An addition that might help someone.

I my case the problem was also white spaces in the key columns. After sorting and using Derived column TRIM() it finally worked.