Post reply

How do I do an inner join where the join conditions are not equal in SSIS?

  • August 22, 2013 at 9:53 am

    #282386

    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

    #1644285

    I'm just going through the basics in SSIS, but can you try lookup transformation ?

  • August 23, 2013 at 2:41 am

    #1644289

    This article explains how you can do a range lookup with SSIS:

    Lookup Pattern: Range Lookups

    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

    #1644565

    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

    #1644789

    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

    #1644814

    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