Merge Join Problem

Hello,
I've got a problem with the merge join operator.

I'm trying to Join two tables using a left outer join.

Here are my tables :

TABLE AGENT
AGENT_CODE; REGION_ID; COUNTRY_ID
AA;01;01;
BB;01;01;
CC;01;02;
CC;02;02;
DD;01;01;

600 records in this table.

TABLE DELEGUE
AGENT_CODE; FIRST_NAME; LAST_NAME
AA; Maradona;Diego;
BB; Maradona;Diego;
DD; Zidane;Zinedine;

145 records in this table.

This is what i expect in my left outer join:
AGENT_CODE; REGION_ID; COUNTRY_ID;FIRST_NAME; LAST_NAME
AA;01;01;Maradona;Diego;
BB;01;01;Maradona;Diego;
CC;01;02;NULL;NULL
CC;02;02;NULL;NULL;
DD;01;01;Zidane;Zinedine

600 records in the destination table, with only 145 Not null first_name and last_name

The issorted property has been set to true in the 2 sources tables, the numkeyscolumns property is set to 1, and the join keys are both checked.
When i execute the sql query using the editor in sql server, the result is good, i've got what i've expected.
But when i run the dataflow, the destination has got 600 records, but the columns FIRST_NAME and LAST_NAME are not null only 8 times, the 592 other lines are NULL.
Could someone explain to me why i lost 145-8=137 FIRST_NAME and 145-8=137 LAST NAME ?
Thank you in advance.

by left join as the table on left has 600 records, therefore it must result 600 rows, i think so

can you also write your query also here

you can also check by sub query that how many rows exactly match in your child table

|||I changed the table names in first post so that anyone could understand (i'm french), the real table names are :
CODE_AGENT (600 records) and DELEGUE (145).
i want to join these two tables using CODE_AGENT.AGENT_ID and DELEGUE.CODE_AGENT

this is the query i made :
SELECT DELEGUE.NOM_DELEGUE, DELEGUE.PRENOM_DELEGUE, CODE_AGENT.NOM_AGENT, CODE_AGENT.CODE_SECTEUR,
CODE_AGENT.CODE_EQUIPE, CODE_AGENT.CODE_USINE, CODE_AGENT.AGENT_ID, CODE_AGENT.REGION_ID
FROM CODE_AGENT LEFT OUTER JOIN
DELEGUE ON CODE_AGENT.AGENT_ID = DELEGUE.CODE_AGENT

The result is exactly what i want, but when i use SSIS, i lost many records of DELEGUE.NOM_DELEGUE, DELEGUE.PRENOM_DELEGUE.|||

You said that the IsSorted property is set to true and the sortkeyposition property is set to 1 but you didn't say that the data was sorted. Can you verify that the data is actually sorted. Setting these properties don't do anything except tell the dataflow that the data is sorted. If it actually is not sorted then the results of your MergeJoin are undefined.

Thanks,

Matt

|||Thank you Matt !
That was the problem, i didn't understand that i had to sort the table for each merge join.
The result is correct!
I added a "Sort" data flow transformation and the result is ok!