Hi, Sorry if this question is very basic as I am a novice in SSIS. Lookup and Merge Inner Join works same in the package. Can you tell me when should we go for Lookuo and when should we go for Merge Inner Join? Is there any differences between them apart from that lookup is fast and sorting of input columns is not required?

See this blog post: http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/SSIS_3A00_-A-case-study-of-using-LOOKUPs-instead-of-MERGE-JOINs.aspx Most of the times I choose the Lookup because it performce better (no sort required, caching). Only in case of very large datasets the lookup will require a lot of memory resources. In that case the Merge join is better, but a SQL join in the source component is better in that case (if source is database) Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

Lookup is as its quite eloquent name states implies we do just a value check, an action happens based on it outcome whereas in merge join we combine records based again on a match. See a case study on using LOOKUPs instead of MERGE JOINs http://consultingblogs.emc.com/jamiethomson/archive/2005/10/21/2289.aspx Merge Join Explained: http://www.ssistutorial.com/create_join_between_datasets_in_ssis.php (the concept is similar to T-SQL's JOIN) Arthur My Blog

Hi, Sorry if this question is very basic as I am a novice in SSIS. Lookup and Merge Inner Join works same in the package. Can you tell me when should we go for Lookuo and when should we go for Merge Inner Join? Is there any differences between them apart from that lookup is fast and sorting of input columns is not required? They work very differently under the covers but I wouldn't consider that to be the main difference. You are incorrect when you say: Lookup and Merge Inner Join works same in the package" Under certai circumstances it may appear that, given the same inputs, they produce the same output but actually that is not the case. The Merge Join is more akin to SQL's JOIN operator in that all rows matching the join predicate on the left will match will all of thsoe matching it on the right. That is not the case with the Lookup which merely stops as soon sa it finds a match. Other than that fundamental difference in behaviour they actually work very differently under the covers too. The links provided earlier by Arthur and SSISJoost should help to explain that point further. Regards Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me