What are the differences between Merge Join and Lo

Question

Hi I'm new to SSIS packages and writing a package and reading up about them at the same time.

I need to convert a DTS into a SSIS package and I need to perform a join on two sources from different databases and was wondering what was the better apporach, to use a lookup or a merge join?

On the surface they seem very similar. The 'Merge Join' requires that the data be sorted before hand whereas the 'Lookup' doesn't require this. Any advice would be very helpful. Thank you.

Answer 1

Merge Join allows you to join to multiple columns based on one or more criterion, whereas a Lookup is more limited in that it only fetches a one or more values based on some matching column information -- the lookup query is going to be run for each value in your data source (though SSIS will cache the data source if it can).

It really depends on what your two data sources contain and how you want your final source to look after the merge. Could you provide any more details about the schemas in your DTS package?

Another thing to consider is performance. If used incorrectly, each could be slower than the other, but again, it's going to depend on the amount of data you have and your data source schemas.