locked
Invalid character value for cast specification in CopyData Activity from MySQL DB RRS feed

  • Question

  • My CopyData Activity to copy data from a MySQL DB to an Azure SQL DB always fails with the following error:

    {
    
        "errorCode": "2200",
        "message": "Failure happened on 'Source' side. ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=MySQLODBC_sb64.dll,''Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=MySQLODBC_sb64.dll,'",
        "failureType": "UserError",
        "target": "CopyData"
    }

    The problem is that I can't even narrow down the error to which rows or fields are causing the issues, because although I chose "Skip and log incompatible rows" as Fault tolerance for the CopyData Activity, the respective blob storage remains empty, i.e. the error stops the activity run without any results.

    How can I find out what exactly causes the problem and resolve it?

    Thursday, May 9, 2019 1:15 PM

All replies

  • Hello JoeTheOne and thank you for your inquiry.  Without knowing enough details to reproduce the issue, I can give the following likely scenarios.

    Cast String -> Numeric or Integer or Float
    There is an unexpected decimal, or non-numeric character such as '$' or '^' or ',' or perhaps misplaced whitspace.

    Cast String -> String
    If the encoding spec isn't the same throughout the whole process, then there may be a character valid in one side but not the other.

    However, given it happens on the source side, possibly before the data even gets to the Factory, the issue could be something less obvious, and specific to the database.  I seem to recall a case where the database took issue with casting while exporting.  I'll have to dig to find that.  If you have only a few columns, you could try changing the type settings.

    Friday, May 10, 2019 1:35 AM
  • Hello Martin Jaffer,

    Thank you for your feedback.

    Unfortunately, the source is rather big with many columns and thousands of rows, and since it is operational data I don't want to make changes to it... is there any way to get more details on the cause of the error without manually looking at each and every field or starting trial & error?

    An information about what row or column caused the error would be a great step forward already.

    Wednesday, May 15, 2019 7:41 AM
  • If you are using a self-hosted integration runtime, you could turn on verbose logging.  Not sure if that would give the exact info you are looking for.

    Do you have "Zero dates" ?  There is a similar thread where this error was caused by dates that used "0000-00-00" instead of null.

    Saturday, May 18, 2019 12:22 AM
  • We have not received a response from you.  Are you still facing the issue?  If you found a solution, would you please share it here with the community?  Otherwise, let us know and we will continue to engage with you on the issue.
    Friday, May 29, 2020 8:38 PM
  • Since we have still not heard back from you, we will assume you found your own resolution.  If you found a solution, would you please share it here with the community?
    Tuesday, June 2, 2020 1:42 AM