none
Can any one guide me lookup and merg join transformation

Answers

  • There is a quite a lot of differences between those two transformations and we can name a few of them

    1. Lookup ins Non Blocking transformation where Merge Join is partially blocking transformation (it split's the execution tree in SSIS)
    2. Merge Join requires that the input streams are sorted on the same columns, while lookup do not requires the data sets to be sorted at all.
    3. The internal mechanism how those two works is completely different. 
           Lookup can work in 3 modes, full cache, partial cache or No cache. In No cache, for each input row a query Is sent into the source to lookup the value. In partial case the query is sent only if data are not covered by cache.. In full cache all the lookup data are fetched into memory prior processing the input stream and then the lookups are done in memory.
           Merge Join processes both input streams at the same times and tries to match them.
    4. You cannot perform FULL OUTER JOIN using Lookup transformation (only INNER and LEFT JOIN)
    5. Lookup can automatically redirect non matched rows to separate output, while Merge Join cannot and you have to use Conditional Split to redirect non matched data.
    6. Merge Join is good for joining large datasets with similar number of rows

    Saturday, July 13, 2013 12:33 PM
  • and the lookup always returns 0 or 1 records... Merge Join can return multiple matches.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Saturday, July 13, 2013 1:47 PM
    Moderator

All replies

  • There is a quite a lot of differences between those two transformations and we can name a few of them

    1. Lookup ins Non Blocking transformation where Merge Join is partially blocking transformation (it split's the execution tree in SSIS)
    2. Merge Join requires that the input streams are sorted on the same columns, while lookup do not requires the data sets to be sorted at all.
    3. The internal mechanism how those two works is completely different. 
           Lookup can work in 3 modes, full cache, partial cache or No cache. In No cache, for each input row a query Is sent into the source to lookup the value. In partial case the query is sent only if data are not covered by cache.. In full cache all the lookup data are fetched into memory prior processing the input stream and then the lookups are done in memory.
           Merge Join processes both input streams at the same times and tries to match them.
    4. You cannot perform FULL OUTER JOIN using Lookup transformation (only INNER and LEFT JOIN)
    5. Lookup can automatically redirect non matched rows to separate output, while Merge Join cannot and you have to use Conditional Split to redirect non matched data.
    6. Merge Join is good for joining large datasets with similar number of rows

    Saturday, July 13, 2013 12:33 PM
  • and the lookup always returns 0 or 1 records... Merge Join can return multiple matches.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Saturday, July 13, 2013 1:47 PM
    Moderator
  • thanks a lot
    Sunday, July 14, 2013 5:07 AM
  • thanks
    Sunday, July 14, 2013 5:07 AM