来自MySQL DB的CopyData活动中的强制转换规范的字符值无效 [英] Invalid character value for cast specification in CopyData Activity from MySQL DB

问题描述

将数据从MySQL数据库复制到Azure SQL数据库的我的CopyData活动始终失败，并出现以下错误：

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"
}

问题是我甚至无法缩小导致问题的行或字段的错误，因为尽管我选择了"跳过并且记录不兼容的行"作为CopyData活动的容错，相应的blob存储保持为空，
即错误停止活动运行而没有任何结果。

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?

推荐答案

您好JoeTheOne并感谢您的询问。 在不知道足够的细节来重现问题的情况下，我可以给出以下可能的情况。

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 - >数字或整数或浮点数
有一个意外的十进制或非数字字符，例如'

Cast String -> Numeric or Integer or Float
There is an unexpected decimal, or non-numeric character such as '

'或'^'或'，'或者可能放错位置whitspace。



Cast字符串 - >字符串

如果编码规范在整个过程中不相同，则可能在一侧有效但在另一侧有效。

' 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.

这篇关于来自MySQL DB的CopyData活动中的强制转换规范的字符值无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！