Question: what is the difference between lookup and merge join transformation in ssis?
Answer: A Merge Join is designed to produce results similar to how JOINs work in SQL. The Lookup component does not work like a SQL JOIN. ... With a Merge Join you will get the desired output. With a Lookup, where input 2 is the look up source, the output will be one row per invoice, no matter how many rows exist in input 2.