0

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

2 Answers 2

0

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.

2
  • I'll try to set manually, and if that doesn't work, I'll resort directly before join and see if they helps. I'll report back findings. Aug 28, 2019 at 4:04
  • 2
    Oddly enough, the output columns for non- derived columns do not show up in the advanced settings, so I had to re-sort both inputs prior to the merge join. Hopefully this helps someone in the future having the same issue. Aug 28, 2019 at 14:40
0

Disconnecting all inputs and reconnecting them to the merge component usually works, but sometimes column metadata from the prior connections is left in the merge component. Verify all mappings, and remove any leftover columns.

Also, use the "resolve references" action, by right-clicking on a data flow path. Remove any unmapped input columns.

I found that the error occurs when a change was made to the metadata for an input column results in, the metadata error. Sometimes the disconnect/reconnect method worked, and other times, it resulted in a different error, complaining that fields were not mapped in the Merge component. Opening the editor and attempting to map the columns then threw an application error in VS. The rows that throw the application error were deleted from the Merge. Doing so resolved the errors.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.