Merge join with empty inputs

Hi all,

Does anyone have suggestions for ways to deal with the chance that a merge join might receive empty inputs?

I've noticed that when this happens the transformation seems to hang. I changed the MaxBuffersPerInput to zero and this seems to cure the problem but I'm not sure it's the best way to deal with it.

Would it be a good idea to test the row counts with a conditional split before such a join?

Cheers,

Andrew

Are you certain that both inputs are generating EORs when they are complete. If so, then this is likely a bug. If not, then there is no way for the MergeJoin to know that it won't be getting buffers so it is not hung it is just waiting for pending input.

HTH,

Matt

|||

I'm pretty sure they did when I ran the package in a cmd window.

Presumably they did after I changed the MaxBuffersPerInput to fix the problem and the data hadn't changed between runs.

I'll need to recreate the state to confirm this which will take a while.

Andrew

|||

Finally got the problem to recur.

In this case it's a simple Inner Join on one column. The left input has no rows coming in, the right has 582 as shown below.

DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Left Input" (1627)
End DataFlow
DataFlow: 2006-07-11 16:06:50.97
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) will receive 582 rows on input "Merge Join Right Input" (1628)
End DataFlow
DataFlow: 2006-07-11 16:06:51.10
Source: DFT Get recent returns from Oracle
Component "MRGJ Match on Oracle Ids" (1626) was given end of rowset on input "Merge Join Right Input" (1628)
End DataFlow

The package runs in BIDS but hangs with the above as the last output when run through DTEXEC. I also tried switching the left/right inputs just in case but the result was the same.

Any suggestions for a clean way to handle this? I'm trying to work out a consistent way to handle merge joins to deal with this sort of situation.

Andrew

|||

Looks like this may have been resolved by a hotfix:

FIX: When you call a SQL Server 2005 Integration Services package from a SQL Server Agent job step, the package may stop responding

CAUSE: This problem occurs when the SSIS package contains a Merge Join transformation.

http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B928243&sd=rss&spid=2855

|||

Did you install the hotfix? Or did you continue to use the 0 MaxInputBuffers property? I just discovered this issues in one of my packages and I'm just curious if that hotfix did fix the problem. The description is just really vuage on the hotfix.

sql