Follow

Error: The locale id of the source column and the locale id of the destination column do not match.

Symptoms

Executing a staging area table results in an error message.

Error Message

The locale id '####' of the source column '<ColumnName>' and the locale id '####' of the destination column '<ColumnName>' do not match.

Underlying Cause

The TX DWA staging area database and one or more of the data source databases are using different collation options.

Solution

Check "Force Codepage Conversion" in the data source settings.  This will ensure that TX DWA is able to translate the source data into a format the staging area database can understand.  

Note that if you add an additional connection that requires this option to an existing data source, you must select "Force Codepage Conversion" in the options for that new connection.  You must take this step even if you've already selected the option in the primary data source's settings.  The reason for this is that additional connections do not inherit any settings from the primary data source.  

Having this option enabled for the main data source but not for an additional connection is a common cause for this error.  

Was this article helpful?
1 out of 2 found this helpful
Have more questions? Submit a request

5 Comments

  • 0
    Avatar
    Blanca Alida Hembre

    I right click on the Data Source, then click on "Data source Settings F4", but I can not find the option "Force Codepage Conversion" to check.

    I am using TX DWA Server 17.8.1.64

  • 0
    Avatar
    Steven Koppenol

    I'm getting this error during transfer from the first DWH to the next DWH. Both have the same collation (Latin1_General_CI_AS)

    Discovery Hub version is 17.12.8

  • 0
    Avatar
    Doug Wynkoop

    Hi Blanca, 

    I apologize for the lateness of this response, but hopefully it can help folks moving forward!  The option for forcing code page conversions is in the Edit data source window, not the Data Source Settings window. 

    Steven,

    Something to keep in mind is that once the database is created, changing the collation in database settings will not actually update the database collation like you might expect.  

    Please check the database collation settings of your two databases, either by looking at database properties in SSMS, or by running the following query against the two databases: 

    SELECT CONVERT (varchar, SERVERPROPERTY('collation'));  

    If the two databases have different collations, you can update them to what they should be in SSMS to make them match, or drop the databases and re-create them through Discovery Hub.

    If the two databases really do have the same collation, please submit a ticket and we'll look into this further.

    Best regards,

    Doug Wynkoop 

  • 0
    Avatar
    Steven Koppenol

    Hi Doug,

    In our case the collations are all the same, except for the TX repositories that don't seem to follow server defaults. We do have a cross-sql-version transfer (from 2016 to 2012)

    Error went away after switching on SSIS transfer.

    For other people reading this:

    Doug's command will show you the server default collation which doesn't have to be the same as the database collation (although you better keep them in line!) To show database collations you can run select name, collation_name from sys.databases.

     

  • 0
    Avatar
    Doug Wynkoop

    Hi Steven,

    Thank you for the update, and the clarification on the effects of my script there!  

    I'm glad this problem seems to be resolved, but of course data transfer should work no matter what method you choose.  

    I'll follow up internally and see if we can discover what might have caused your issue.

    Best regards,

    Doug Wynkoop

Please sign in to leave a comment.