August 22, 2013 at 9:53 am
Hi,
I've been trying to figure out an elegant way to do a merge join in SSIS where the join conditions are not equal... I know there an easy solution that's just escaping me right now, and I can't find anything on the net...
Any help would be appreciated 🙂
I basically have one dataflow with values and amounts:
code min max
1 0 100
2 101 200
3 201 300
And a table where I need to lookup or join to get the right code for each record:
ac value code
1 135
2 55
3 65
4 201
etc....
In sql, I woudl just to an inner join where value >= min and value <= max but I can't see how to do this in ssis.
Thanks
August 23, 2013 at 2:35 am
I'm just going through the basics in SSIS, but can you try lookup transformation ?
August 23, 2013 at 2:41 am
This article explains how you can do a range lookup with SSIS:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 23, 2013 at 2:11 pm
Thanks very much for your reply. Very helpful.
Looks like there's no real elegant way to do this (besides going through teh trouble to write a custom script) although I think I'll try the Merge Join - Conditional split.
It's really too bad SSIS doesn't support this in a regular Merge Join component...
In playing around, I have been just saving the values to a Temp table, and then doing my own update in the Control flow where I can then write in the sql code for the join, but this just breaks up all the processing and is really a work around.
Thanks!!
Sarah
August 25, 2013 at 11:38 pm
sarah.cook (8/23/2013)
Thanks very much for your reply. Very helpful.Looks like there's no real elegant way to do this (besides going through teh trouble to write a custom script) although I think I'll try the Merge Join - Conditional split.
It's really too bad SSIS doesn't support this in a regular Merge Join component...
In playing around, I have been just saving the values to a Temp table, and then doing my own update in the Control flow where I can then write in the sql code for the join, but this just breaks up all the processing and is really a work around.
Thanks!!
Sarah
That may be so, but doing joins using the SQL Server engine is much faster than doing them in SSIS. So it may feel like a workaround, but perhaps if you instead consider it as a performance optimisation things won't seem so bad 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 26, 2013 at 2:06 am
Phil Parkin (8/25/2013)
That may be so, but doing joins using the SQL Server engine is much faster than doing them in SSIS. So it may feel like a workaround, but perhaps if you instead consider it as a performance optimisation things won't seem so bad 🙂
If the data is already sorted, SSIS isn't necessarily slower for large data sets.
If the data is sorted. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply