merge join going wrong

Question

question

FARZZ asked Jun 3, '10

merge join going wrong

select mr.institution_name,
mr.institution_id,
mr.oppty_id, 
mr.start_date, 
mv.startdate
from mr_staging as mr
inner join ceb.dbo.MRvw_memmismatch as mv 
on mr.oppty_id = mv.opptyid
where  
mr.start_date <> mv.startdate

I execute this query and get 80 rows..which is the mismatch data.
That is ok…
Now I try to implement this is ssis
I have two ole db sources, for both tables.then I sort them on the oppty_id
Then I do merge join, then I have a condition split where i give the above condition then I collect the data in aa excel file.

But when I execute the task it does not do the merge join and I have no data in the excel sheet… where am I going wrong.

ssis t-sql merge

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-03T21:37:46Z

Daniel Ross answered Jun 3, '10

Firstly make sure the data is coming from the sources correctly, put data viewers on the package after the sorts to check the data is correct.

On the join, watch out because the join IS case sensitive, but if it is a number it should be fine.

By default, the merge join DOES NOT pass through all the data, you have to select the columns that you want to pass through the join. Make sure the fields you want to go through are listed in the out table underneath the join boxes. If not, check the boxes to the left of the name field in the join editor.

If the data is being passed correctly, put data viewers on the data flow path after the join and look at the data. If there is no joining going on, then change the join type to left outer join and test again and if needed swap inputs.

If the data is going to the conditional split OK, then check the expression is correct. should be something like

[start_date]!=[start_date(1)]

if there is no data from the conditional split, check the data from the default output and check that.

Other than that, make sure the datatypes for the join fields and the dates are the same.

And if all else fails from that, you can just use your original SQL with the join and use that in the data source.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question