Merge Join transformation hangs

I have serached through this forum, and I could not find the solution.

My SSIS data flow is a simple one. Extracting two tables from Oracle using OLE DB Source , join them together using merge join and loading into a table in SQL server by SQL Server Destination.

I haven't gone through this simple procedure because Merge join always hangs there. Down to further investigation, I found one input (randomly one of two inputs) is always stuck. Sometimes the input is empty, sometimes is about half way.

Is there workround to see what is happening there and to fix this problem?

TIA

As far as I can see, Mergejoin is not the culprit, it is by design for MergeJoin to wait until it gets buffers from both inputs before it outputs. Looks to me your input(s) did not get through at your Oracle server. I suggest you to use an external tool (rowsetviewer?) to perform the same queries you put on the two OLEDBSrc inputs, to see whether it works there.

thanks

wenyang

|||

Also make sure the 2 inputs of the Merge Join are actually sorted.

Rafael Salas

|||

Hi, Wenyang & Rafael,

Thanks very much for your input.

The 2 inputs of the Merge Join are sorted by "order by" and the Issorted = true. I found the killer is the "data viewer" of the path, which shows the input data. Once I removed the "data viewer", the problem's gone. I cannot explain why.

BTW, what's the external tool (rowsetviewer)? Where can I get it?

Merge join

Hi, folks:
I am now testing merge join with results not expected.
I have two OLE data source (one from Oracle, one from SQLSERVER)
then I do a merge join with the key LOCATION_ID

OLE DATA SOURCE 1 OLE DATA SOURCE 2
-- --

Location_id volume location_id shortname

123 3.5 256 well_far_away
256 4.6

My expected result for my inner join is
256 4.6 well_far_away
But I got no rows

If I change it to full outer join
I got this

123 3.5 null
256 4.6 null
null null well_far_away
What did I do wrong and should I do a merge join like this?

Well from the outer join results it appears that mergejoin is not detecting that the location_id from data source 2 matches either of the rows on data source 1. Can you add the location_id from data source 2 to the output (for testing purposes only, of course) and see what the value is displayed as? You could also put a data viewer on the path from data source 2 to the mergejoin and see what the value coming into the mergejoin is. Looking at this I would guess that the location_id from data source 2 is not what you are expecting, but the only way to know is to look at it.

HTH,
Matt

|||

A simple Merge Join from two OLE-DB sources with the data you describe works fine for me. Is that really your data, if not try it, since it does work. Then described what you are doing in more detail perahps.

A misunderstaning I've seen on the forums and in person several times this week is the IsSorted and SortKey properties. If you set the sort properties by hand, and the data is not sorted then this will fail. Setting the properties does not induce a sort operation, it just describes how the data has already been sorted within the component. For SQL sources, make sure your ORDER BY clause matches the properties.

|||You guys are wonderful, I used the data viewer to look at the OLE source 1 and realized that even though I set the issorted=1, I did not have a order by clause in my SQL statement. Excaltly like what DarrenSQLIS said.I put in my order by clause and it works as expected. BTW, I am going to Vancouver next week attend a 4-day training on SQL server 2005, anyone else going.