I'm trying to create a merge join that essentially does this:

SELECT DISTINCT a.CustomerID
,a.AddressID
,b.SalesOrderID
,b.SalesOrderDetailID
FROM
(
SELECT Sales.Customer.CustomerID
,Sales.CustomerAddress.AddressID
FROM Sales.Customer
INNER JOIN Sales.CustomerAddress
ON Sales.Customer.CustomerID = Sales.CustomerAddress.CustomerID
) a
INNER JOIN
(
SELECT Sales.Customer.CustomerID
,Sales.SalesOrderHeader.SalesOrderID
,Sales.SalesOrderDetail.SalesOrderDetailID
FROM Sales.SalesOrderHeader
INNER JOIN Sales.Customer
ON Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID
INNER JOIN Sales.SalesOrderDetail
ON Sales.SalesOrderHeader.SalesOrderID =
Sales.SalesOrderDetail.SalesOrderID
) b
ON a.CustomerID = b.CustomerID

Each subquery in my example ecomes a separate OLE DB source in my SSIS
package. When I run the T-SQL code, I get 122579 rows. However, when I
run the merge join, I only get 17 rows. Why is that? I believe the
join in the Merge Join package is correct.

Hello imani_technology_spam (AT) yahoo (DOT) com,

Why are you handing this off to SSIS? You should be doing this (if it is
as simple as it looks) as a SQL Command in your Source Adapter.

In the Merge Join you are joining on CustomerID on each sorted input?

Why are you qualifying the DB? That costs you.


Have you counted the rows going in to each input?


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I'm doing this as an experiment for work. My bosses wanted me to do a
quick comparison of a merge join to a T-SQL approach.

Yes, I am joining on CustomerID on each sorted input?

The rows going into each input are correct. The number of rows coming
out of the merge join is incorrect.

I'm not quite sure what you mean by "qualifying the DB."

Allan Mitchell wrote:

Hello imani_technology_spam (AT) yahoo (DOT) com,

You can use TSQL from within SSIS, you do not need to go all procedural and
row based. They compliment each other. TSQL will be quickest in a lot of
questions.

You specify the Db in your qiery. Why? If you execute this in the correct
Db you do not need to do this. SQL Server adds overhead when you do it.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com