none
Copy data activity - ErrorCode 2200 - UserErrorColumnNameMismatchByCaseSensitive RRS feed

  • Question

  • I have the following error output from a copy data activity:

    {
        "errorCode": "2200",
        "message": "ErrorCode=UserErrorColumnNameMismatchByCaseSensitive,
    'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 
    'MyFieldName' in DataSet '[tblMyTable]' cannot be found in physical Sql database. 
    Column matching is case sensitive. Column 'Myfieldname' appears similar. Check the 
    DataSet(s) configuration to proceed further., Source=Microsoft.DataTransfer.ClientLibrary,
    ''Type=System.InvalidOperationException,Message=The given ColumnMapping does not match 
    up with any column in the source or destination.,Source=System.Data,'",
        "failureType": "UserError",
        "target": "copy-table"
    }

    I can neither find the UserErrorColumnNameMismatchByCaseSensitive error mentioned anywhere, nor can I find a property/setting for an Azure Data Factory dataset which mentions case-sensitivity.

    Ideas?


    Friday, November 1, 2019 6:43 PM

Answers


  • Is there a way to turn off that case-sensitivity so that a column named MyField will be matched successfully with a column named myField?  Considering that the SQL Server engine has been tolerant of casing within objectnames since forever, this doesn't sound like something unreasonable.

    Looks like case sensitivity is there by design. 

    You may upvote below feedback:

    Mapping of column names should be case-insensitive in SQL Azure Connector of ADF


    Regards,
    Vaibhav

    Tuesday, November 5, 2019 2:35 PM
  • Hi 

    Sorry for the inconvenience, please send a note to verify your MSDN account in below discussion thread, MSDN team will verify your account and then you can post images. 

    https://social.msdn.microsoft.com/Forums/en-US/dc4002e4-e3de-4b1e-9a97-3702387886cc/verify-account-42?forum=reportabug 

    Going back to your issue, thanks for your response and sharing the details.
    By looking at the error message: Column 'SubSystem' in DataSet '[tblASTM]' cannot be found in physical Sql database. Column matching is case sensitive. Column 'Subsystem' appears similar. Check the DataSet(s) configuration to proceed - It error-ed because in source table the column name is "SybSystem" where both 'S' are capital letters and in the sink (target) table the column name is having one Capital and one small 'S' (Subsystem). I was able to reproduce this error and resolved by fixing the column name in sink table to match exactly the same as source column name since column matching is case sensitive in ADF by design. 


    Source Column Name Sink Column Name Copy Result
    SubSystem SubSystem Success
    SubSystem Subsystem Errored/Failed
    MyFieldName MyFieldName Success
    MyFieldName Myfieldname Errored/Failed


    Hope this is helpful. Please let us know if you have any further query. 



    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Tuesday, November 5, 2019 12:35 AM
    Moderator

All replies

  • Hi JohnPRoberts3rd,

    Thank you for your query. Could you please provide what is your source and sink datasets? Are you doing an "Auto-mapping" or "Explicit mapping" in your copy activity? 

    By looking at the error message, I assume that there might a mismatch in Columns names of Source & Sink tables. Could you please verify that?


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, November 2, 2019 12:31 AM
    Moderator
  • Hi JohnPRoberts3rd,

    Following up to see if you have got a chance to see my previous comment, If so, could you please share details requested?


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, November 4, 2019 6:47 PM
    Moderator
  • 1.  I am using auto-mapping since the copy table activity is inside a ForEach which is looping through all of the tables in the database.  Explicit mapping won't work in this case since the columns change on each iteration of the parent ForEach activity.

    2.  There is no mismatch in column names, positions, or objectname casing.  The target database tables were scripted from the source tables originally.

    3.  Here is the corresponding "casing" error without alteration.  Note that in neither the source nor target table above does the casing match what's in the error message.

    {
        "errorCode": "2200",
        "message": "ErrorCode=UserErrorColumnNameMismatchByCaseSensitive,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'SubSystem' in DataSet '[tblASTM]' cannot be found in physical Sql database. Column matching is case sensitive. Column 'Subsystem' appears similar. Check the DataSet(s) configuration to proceed further.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidOperationException,Message=The given ColumnMapping does not match up with any column in the source or destination.,Source=System.Data,'",
        "failureType": "UserError",
        "target": "copy-table"
    }

    4.  Using the table that this error is being generated for, I created a separate pipeline with just a single copy data activity and loaded in its schema (explicit mapping), but there's still no obvious property that has anything to do with turning on/off case-sensitivity.

    ...and on a side note, I tried to include screenshots of these things, but for whatever reason, this forum is preventing me from posting images "until my identity is verified", but nowhere in the profile page is there any obvious way of doing that.

    Monday, November 4, 2019 8:18 PM
  • Hi 

    Sorry for the inconvenience, please send a note to verify your MSDN account in below discussion thread, MSDN team will verify your account and then you can post images. 

    https://social.msdn.microsoft.com/Forums/en-US/dc4002e4-e3de-4b1e-9a97-3702387886cc/verify-account-42?forum=reportabug 

    Going back to your issue, thanks for your response and sharing the details.
    By looking at the error message: Column 'SubSystem' in DataSet '[tblASTM]' cannot be found in physical Sql database. Column matching is case sensitive. Column 'Subsystem' appears similar. Check the DataSet(s) configuration to proceed - It error-ed because in source table the column name is "SybSystem" where both 'S' are capital letters and in the sink (target) table the column name is having one Capital and one small 'S' (Subsystem). I was able to reproduce this error and resolved by fixing the column name in sink table to match exactly the same as source column name since column matching is case sensitive in ADF by design. 


    Source Column Name Sink Column Name Copy Result
    SubSystem SubSystem Success
    SubSystem Subsystem Errored/Failed
    MyFieldName MyFieldName Success
    MyFieldName Myfieldname Errored/Failed


    Hope this is helpful. Please let us know if you have any further query. 



    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Tuesday, November 5, 2019 12:35 AM
    Moderator
  • Ok.  I get it.  Thanks for checking that out.

    Now back to part of the original question.  I could not find any way to turn off case-sensitivity within the "copy data activity".

    Is there a way to turn off that case-sensitivity so that a column named MyField will be matched successfully with a column named myField?  Considering that the SQL Server engine has been tolerant of casing within objectnames since forever, this doesn't sound like something unreasonable.

    Tuesday, November 5, 2019 2:16 PM

  • Is there a way to turn off that case-sensitivity so that a column named MyField will be matched successfully with a column named myField?  Considering that the SQL Server engine has been tolerant of casing within objectnames since forever, this doesn't sound like something unreasonable.

    Looks like case sensitivity is there by design. 

    You may upvote below feedback:

    Mapping of column names should be case-insensitive in SQL Azure Connector of ADF


    Regards,
    Vaibhav

    Tuesday, November 5, 2019 2:35 PM
  • Thanks for sharing the feedback link here Vaibhav Chaudhari!

    @JohnPRoberts3rd, Currently there is no way to turn off the case sensitivity in column matching of Copy activity, it is by design as mentioned by Vaibhav. I have followed up with internal team regarding your request and got an update that ADF team is aware of this and they had it in their long term plan. 

    But I would recommend you to please up-vote or comment on the feedback suggestion shared by Vibhav, so that it would help in prioritizing the feature request. 

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Tuesday, November 5, 2019 11:28 PM
    Moderator