Azure Data Factory MySQl to SQL Sink error: 'UInt64' in not supported

Question

I am having the following problem when I try to sink data from on premise MySQL database to Azure SQL database:

Failure happened on 'Sink' side. ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'The type of column 'id' is not supported. The type is 'UInt64''.,Source=,''Type=System.ArgumentException,Message=The type of column 'id' is not supported. The type is 'UInt64',Source=Microsoft.Data.SqlClient,'

I defined a Table type in the Sink side

The source Dataset previews the data fine, but when copy activity runs i get the above error:

I tried changing the json mapping to sync data types between source and SQL sink but still getting the same error. My source dataset uses a query, i.e. select * from [mysql_schema].[table]. If i change the query to a simple cast on the specified column, i,e. select cast(id as nchar), [col2],... from [mysql_schema].[table] it works fine, but i need to be able to issue a select * from.. statement to parameterize my pipeline. How should i proceed?

Best regards

Answer 1

@sakis giannou

Thanks for using Microsoft Q&A forum and posting your query.

It looks like you’re encountering a common issue when transferring data from MySQL to Azure SQL Database using Azure Data Factory. The error message indicates that the UInt64 type from MySQL is not supported in Azure SQL, which typically uses INT or BIGINT for integer types.

Here are a few approaches you can consider to resolve this issue:

1. Modify the Data Type in the Source Query: Since you mentioned that casting the id column works, you could create a view in your MySQL database that casts the id column to a compatible type (like BIGINT). Then, you can use this view in your SELECT * statement. For example: SQL

      CREATE VIEW my_view AS SELECT CAST(id AS SIGNED) AS id, col2, ... FROM [mysql_schema].[table];
   

   Use a Mapping in Azure Data Factory: If you want to keep using SELECT *, you can define a mapping in the Copy Activity settings. In the mapping section, explicitly map the id column to a compatible type in Azure SQL (like BIGINT).
2. Edit the JSON Payload: If you’re comfortable with JSON, you can directly edit the JSON payload of the Copy Activity to specify the data type for the id column. This can be done in the Azure Data Factory UI by switching to the code view.

Check Driver Versions: Ensure that you are using the recommended MySQL driver version in Azure Data Factory, as this can affect data type compatibility.

If you need to maintain the flexibility of using SELECT *, the view approach is often the most straightforward

Hope this helps. Do let us know if you any further queries.