SSIS Lookup join and SQL join

Question

Joining 2 tables using the ssis lookup would result only the first matching output,rather than all the matching output in SQL.Is there any way to code SSIS ,to result out all the matching out put.

Table 1

Country,Prate,year
uk,1%,2014
France,1%,2014

Table 2

Country,SSrate,year
uk,11%,2014
uk,22%,2013
France,15%,2014

The ssis result, with the joining column as country would be

   Country,Prate,year,ssrate
    uk,1%,2014,11%
    France,1%,2014,15%

How to get a result as shown below

Country,Prate,year,ssrate
uk,1%,2014,11%
uk,1%,2013,22%
France,1%,2014,15%

score 1 · Accepted Answer · edited Apr 13 '17 at 12:42

Eyeballing it, I would reverse your operations.

I would have an OLE DB Source set as Table2 and then use Table 1 in a Lookup Component. Thus, every row from Table 2 makes it to the destination with data from Table 1 appended to it.

If there is data in table 1 that needs to make it to the target, you'd need to update the question to indicate all the cases you need to deal with. But, the answer at that point would probably be something like this

SSIS Lookup join and SQL join

1 Answers1