Using SSIS to do a between query

Question

I am wondering if there is a way of doing a between query join in SSIS without using a temp table on my server.

Given two tables, Accounts and Groups. Accounts contains a list of Accounts with a an upper and lower range to define a list of customers. Groups contain all the customers.

I want to be able join the tables so that I get all the accounts and the customers attached to those accounts. If it was just one account I required, I could use a conditional split however I want all accounts and all customers from the groups.

The tables come from separate systems so placing the query in one datasource is not an option.

If the tables were on the same system I could use the following query.

SELECT 
    Accounts.Account,
    Groups.Customer
FROM
    Accounts,
    Groups
WHERE
    Accounts.AccountType = 1 AND
    Groups.GroupName BETWEEN Accounts.LowerGroup AND Accounts.UpperGroup

Any ideas on how to do this in a SSIS package. I really dislike my current method of saving both tables out to the server but I am struggling to find a way to do it in a single data flow task.

Answer 1

I assume each customer belongs to a single account, and the group ranges don't intersect?

Then I would do something like this:

1) Sort both tables by Group (just use the LowerGroup from Accounts table)

2) Full Merge Join by Group. This will give you table like

 Account Group Customer
 A1      G1    NULL
 NULL    G2    C1
 NULL    G3    C2
 A2      G4    NULL
 NULL    G5    C3
 NULL    G6    C4

3) A simple script transform that will do the following:

  If Customer is NULL 
    Store the Account to member variable, and skip this row
  Else
    Copy stored Account to the Account column, output the row

You'll get

 Account Group Customer
 A1      G2    C1
 A1      G3    C2
 A2      G5    C3
 A2      G6    C4