Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I do an inner join where the join conditions are not equal in SSIS? Expand / Collapse
Author
Message
Posted Thursday, August 22, 2013 9:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:41 PM
Points: 4, Visits: 16
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
Post #1487372
Posted Friday, August 23, 2013 2:35 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 4, 2014 7:40 AM
Points: 3,546, Visits: 2,652
I'm just going through the basics in SSIS, but can you try lookup transformation ?
Post #1487685
Posted Friday, August 23, 2013 2:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 13,735, Visits: 10,705
This article explains how you can do a range lookup with SSIS:

Lookup Pattern: Range Lookups




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1487692
Posted Friday, August 23, 2013 2:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:41 PM
Points: 4, Visits: 16
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




Post #1488013
Posted Sunday, August 25, 2013 11:38 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 15, 2014 7:23 AM
Points: 5,074, Visits: 11,852
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





Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1488286
Posted Monday, August 26, 2013 2:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 13,735, Visits: 10,705
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1488318
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse