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%
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