SharePoint 2010 Managed Metadata Import Error "Bulk load data conversion error (CustomSortOrder)"

What?

You receive the below error when you try to Export/Import Managed Metadata Service Application from one farm to another.

Import-SPMetadataWebServicePartitionData : Bulk load data conversion error (tru

ncation) for row 11, column 7 (CustomSortOrder).

One of the easiest ways to backup/restore Managed Metadata Service Application from one farm/environment to another is using import/export functionality.

I have explained this procedure a little while ago in this blog post

I have been successfully using the Import/Export method with no issues until I stumbled across this error today.

Why?

As the error hints, the error occurred due to specifying custom sort order in one of the term sets.


How?

When I exported the managed metadata service application from one farm and tried to import it in a different farm, I received the below error:

Import-SPMetadataWebServicePartitionData : Bulk load data conversion error (tru

ncation) for row 11, column 7 (CustomSortOrder).

 As shown in the screenshot below, a term set has custom sort order specified to always show "USA" at the top of the countries list.

To resolve the issue, choose the option "Use default sort order according to current language." and the term store will default to use the default sort order which is ascending order.

Then run the command Export-SPMetadataWebServicePartitionData to export the Managed Metadata Service Application.

Once the export process completes successfully, specify the custom sort order on the term set(s) again in the farm where the backup was taken.

Now run the Import-SPMetadataWebServicePartitionData in the target farm and the import process should be successful.

Specify the term store administrator(s) and navigate to the term set(s) to which custom sort order must be specified.

You will see that the owner of the term set(s) will be set to the user from the farm where the backup has been taken (devspconfig is the owner of the term set in the source farm):

We need to change the owner of the term set in the destination farm to SP_Connect. Replace the old owner with the new owner (SP_Connect):

Next step would be to specify the custom sort order again on the term set(s). Click on the term set(s) and then click on the tab "Custom Sort".Choose "Use custom sort order". The page will reload and will display all the terms in the term set. Specify the sort order for labels as needed.

Good luck :)