SSIS Merge Join - Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS
I was working on one of my client requirement where I need to split the flat file data based on the conditions and then add join that data with reference data (again a flat file). I used the MergeJoin task to achieve this. Unfortunately I ended up with this error. I tried most of the options available on the internet but none of them worked for me because my mistake was silly I set a property of “Sort” task in one not the other. That’s why MergeJoin was throwing “Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata SSIS” error.
Lets first discuss about the solution of my problem
Root Cause – The main problem was related to the Sort task. I selected the “Comparison Flags” option in one Sort task but not in other task because of that MergeJoin was throwing the error.
Resolution – I selected the “Comparison Flags” option in both the Sort task.
Lesson Learnt – Also compare the both sort properties before looking for solution on internet.
During my research on this issue I gain the knowledge of many other things those I would like to highlight in this post so that it help somebody to make a correct choice between MergeJoin and Lookup.
MergeJoin
1. It is quick and dirty. It work quite well for small set of data.
2. It allow you to join two set of data in 3 different types (Inner, Left Outer Join, Full Outer Join).
3. It only works on sorted data (Either use Sort task or set IsSorted =true in your source data).
Lookup
1. Performance wise it is far better than MergeJoin but it will give you the matching set of data.
2. Works pretty well on both (Sorted and Non Sorted) data.
3. Option to use the cached data set.
No comments:
Post a Comment