Pbm with a Merge Join

[Gaelle2]

Hi everybody!

I'm not an expert using SSIS and something strange append in my package.

I want to join a small table with a huge view.
I use a "Left outer join" because I need the data corresponding with the small table from the view.

My problem is that the merge don't find any matching data in the huge view for the data in the small table.
I check in the database, data exists in the view.

I add a data viewer between the "OLE DB Source" on my view and the "Merge Join" component and the number of rows dislayed in the data viewer are significantly smaller than the number of rows I have in the database!
I when I ask to SSIS to continue to process, the number of rows increased up to the correct value in base...

Is it possible that it's too long for SSIS to compute the total view before executing the merge ?
Can I ask it to wait ?

Thank for your help!

Gaelle

 

[Catadmin]

Which side of the join is the table on and which side of the join is the View on?

Left Outer Join indicates you want all of the data from the object on the left side of the join statement and only matching data from the object on the right side of the join. But it seems to me that you want all the data from the table and only the data in the view which matches the table, so why aren't you using an INNER JOIN?

And the problem could be in your WHERE clause. I've found that if I'm using multiple items in my WHERE clause on a JOIN and one doesn't match at all, I get back a bad recordset.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Gaelle2]

My aim is to load data in my database.
So I want to check which data is already in my database and which is not.

I'm using a LEFT OUTER JOIN to get the "value" column from my view that match with each of my table line. If the value is not null it's an update, if the value is null it's an insert.
I would have used an INNER JOIN if I wanted to get *ONLY* data from my table that match with data from my view.

I don't think the "Merge Join" object have bag parameters...

When I look at my Data Flow Task, the table source box became green, and the view box is still yellow while the number of lines on the link between view and merge increasing. As one of its entry is not completed, the merge box is yellow.
But the folowing boxes became all green ono after one while the view source stay yellow!

I would like SSIS to wait that the merge became green before continuing the process.
Is it possible ?
Why SSIS doesn't wait until the merge box became green and then continue the process ?

This doesn't seems logical to me...

 

[Catadmin]

As far as I know, Source is always processed first. If you want to check the View first, you're going to have re-arrange things and change your precedence constraints.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Gaelle2]

I make a screen shot of what I see when I execute my package.

My question is : is it normal to have that :

Why are there green boxes *after* a yellow box ?
I just would like that SSIS waits until the merge box ends to continue...

By the way, thanks a lot Catadmin for your quick answer!

 

[Catadmin]

SSIS's flow logic is a little weird. Check the Output Window in BIDS as you're debugging and you'll see exactly what it's doing and why.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Gaelle2]

The Output Window display messages about the "Control Flow" elements. There is no details about the execution of the elements of the "Data Flow Task".

Can I specified an option to have more details ?

I think I will change my process and use an OLE DB source with a request. So I will do the JOIN myself.

I'm surprised that nobody have had a similar problem before... Only Catadmin answered me but he seems to have a good experience on SSIS.