Hi I run this in SQL we get 2 values (rows) for t=2 returned in the recordset: create table t1 (t int) insert into t1 (t) values (1) insert into t1 (t) values (2) insert into t1 (t) values (3) create table t2 (t int) insert into t2 (t) values (1) insert into t2 (t) values (2) insert into t2 (t) values (2) insert into t2 (t) values (3) select * from t2 inner join t1 on t1.t = t2.t Question: would using the lookup component in SSIS provide the same result ? Thanks BiF

this is mainly based on what table you set as source and what table you set as lookup table. if you set t1 as source, then you have only 3 output rows if you set t2 as source , then you have only 4 rows in output and Note that default setting for lookup transform will fail when there will be no match for the source table in the lookup table. but you can change this behavior with set "redirect no match rows to no mach output" setting. actually the lookup is intended to find equivalent rows of an FK in the source table from a lookup table, and not intent to join two inputs, but it performs like join, If you just want to join two inputs it is better to user "Merge Join Transform", just you should note that sources should be sorted before merge join transform, and in the join transform you can select "inner/left/outer" join types.http://www.rad.pasfu.com

Hi I run this in SQL we get 2 values (rows) for t=2 returned in the recordset: create table t1 (t int) insert into t1 (t) values (1) insert into t1 (t) values (2) insert into t1 (t) values (3) create table t2 (t int) insert into t2 (t) values (1) insert into t2 (t) values (2) insert into t2 (t) values (2) insert into t2 (t) values (3) select * from t2 inner join t1 on t1.t = t2.t Question: would using the lookup component in SSIS provide the same result ? Thanks BiF No. An INNER JOIN will join everything from the left that matches everythign on the right i.e. It can return many matches. The Lookup component will only return one match - the first one it findshttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me