We have been using Merge join transformation to merge the data from two different sources. I would like to tell you few points that needs to be taken care of while using merge transformations:
- We know that for merge transformations, the two inputs needs to be sorted. For that, we set IsSortProperty of the source to True and set SortKeyPosition of the input column as well on which we want to sort the data. However, this configuration does not suffice the Sort criteria. Merge join actually needs the data itself to be sorted. So do remember to add ‘Order By‘ clause in your source query
- If you have an Identity column in your destination, then make sure the data type of the column is ‘int‘ not ‘bigint’. If the data type of the Identity column is bigint, then merge join will fail.