Merge Join SSIS Throwing Error Despite Sort and Matching Meta

ssis

I'm attempting to Merge Join two data sources. I ensured the metadata matched and that the sort key was present in the metadata, but it is still giving me the error:

Both inputs of the transformation must contain at least one sorted column, and those columns must have matching metadata."

All of the support articles and matching forum questions all mention metadata and sorting. I even type cast the right data set to match the left… Not sure what to do here. Flow and metadata are pictured. First is the left join, second is the right join. The last entry is the column I wish to join on in both metadata tables.

Data Flow
Input1 Metadata
Input2 Metadata

Best Answer

There is a setting where you can tell the Merge Join that the incoming information is sorted, although in the documentation, the Sort task is supposed to properly set these properties. I wonder if it's because you have other tasks after the sort, and the IsSorted property is getting set back to False.

There are two important sort properties that must be set for the source or upstream transformation that supplies data to the Merge and Merge Join transformations:

  • The IsSorted property of the output that indicates whether the data has been sorted. This property must be set to True.

Important

Setting the value of the IsSorted property to True does not sort the data. This property only provides a hint to downstream components that the data has been previously sorted.

  • The SortKeyPosition property of output columns that indicates whether a column is sorted, the column's sort order, and the sequence in which multiple columns are sorted. This property must be set for each column of sorted data.

If you use a Sort transformation to sort the data, the Sort transformation sets both of these properties as required by the Merge or Merge Join transformation. That is, the Sort transformation sets the IsSorted property of its output to True, and sets the SortKeyPosition properties of its output columns.

However, if you do not use a Sort transformation to sort the data, you must set these sort properties manually on the source or the upstream transformation.

The documentation tells you how to set those settings manually.

Related Question