Merge join not matching "correctly"

And by correctly, I mean the way it *should* match, of course.

I've got 2 data sources, using a left outer join, matching 2 columns. Whatever is the right side of my table is never matching and returning data. Here's my basic setup:

OLE DB Source 1 (table1)
Sel Name Order JoinKey
no UniqueID 1 yes
yes Column1 0 no
...

OLE DB Source 2 (table2)
Sel Name Order JoinKey
yes ID 1 yes
yes Columns 0 no
...

There is a link (arrow) between UniqueID and ID, and the join type is "Left Outer Join". When I execute the statement "SELECT * FROM Table1 LEFT JOIN TABLE2 ON TABLE1.UniqueID = Table2.ID", the data returns correctly to me. What am I missing with the properties I've set above with the merge join?

some more information... my two queries are from variables...

"select * from Table1 ORDER BY UniqueID ASC"
and
"select * from Table2 ORDER BY ID ASC"

|||

Are the join columns the same data type?

I set up a similar scenario, and it's working fine. It looks like you've set your SortKeys, etc, so it should work.

Have you added data viewers before the merge join to verify the data is what you expect?

|||Just a general observation: it seems very odd to only be selecting the key from the right side in a left join. Maybe you have a reason for doing it that way, but as a troubleshooting step I'd swap it to the left side to see if that changed anything.

I don't know what data types your ID columns are or if this even applies, but the SSIS components perform case-sensitive matches while SQL Server's default collation is case-insensitive. This allows things that match in SQL not to match in SSIS.
|||

papalarge wrote:

And by correctly, I mean the way it *should* match, of course.

I've got 2 data sources, using a left outer join, matching 2 columns. Whatever is the right side of my table is never matching and returning data. Here's my basic setup:

OLE DB Source 1 (table1)
Sel Name Order JoinKey
no UniqueID 1 yes
yes Column1 0 no
...

OLE DB Source 2 (table2)
Sel Name Order JoinKey
yes ID 1 yes
yes Columns 0 no
...

There is a link (arrow) between UniqueID and ID, and the join type is "Left Outer Join". When I execute the statement "SELECT * FROM Table1 LEFT JOIN TABLE2 ON TABLE1.UniqueID = Table2.ID", the data returns correctly to me. What am I missing with the properties I've set above with the merge join?

Papa,

In the top of the sugestions above:

Are the OLE DB source components joined directly to the Merge join or there are other transfoms in between? The second could alter the original order of the rows.

Have you tried placing a sort transfom before each merge join input? I know you are using a order by in the queries; but just to test it.

BTW,

Why are you using Merge Join?

I noticed in your original post that you can run a query where you actually join the 2 tables in a single query. Did you know you could place the same query in a single OLE DB Source component? That would give you better performance and less trouble. If a single query is not an option; what about having a 1 table OleDB source component that goes to a Lookup transform to add the columns from the 2nd table.

|||Can't believe I didn't think of that. Yeah, just ended up using a merge in sql, and not through the IDE. Thanks for the help all... still don't know exactly why the merge results weren't connecting. I guess the next step was going to be getting a data view control working so I could see exactly what's returning.

Thanks again...
sql