How can I get all matching rows from lookup using full cache mode?

Question

I need to make a lookup between two tables T1(A,B,C) and T2(A,B,C,D,E) on column C to get all column B values that are matching :

T1 :

T2 :

When I choose Full cache Mode I get only the first matching row (I'm only interested by column B values): 12122 but I need to get also 12123 and 12124 because C matches also with these rows.

I've tried to use Partial and no cache modes by using custom query with inner join (which returns all needed rows when executing the query in SSMS) but doesn't return all rows and it's killing performence.

I've tried also the solution proposed here :

How to get unmatched data between two sources in SSIS Data Flow?

And it gives the same results as lookup plus, I need to redirect unmatched rows to new table.

Answer 1

I don't think the cache mode will affect your result, and it is performance based. The ultimate explanation is:

•If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the lookup query. If multiple matches are found, the Lookup transformation generates an error or warning only when the transformation has been configured to load all the reference dataset into the cache. In this case, the Lookup transformation generates a warning when the transformation detects multiple matches as the transformation fills the cache.

To get the matched B from T2, you can just use the SQL in OLE DB source(command), for example:

SELECT distinct T2.B 
FROM T1 as A 
INNER JOIN T2 as B
ON B.C = A.C