question

bennywong-0956 avatar image
0 Votes"
bennywong-0956 asked HimanshuSinha-MSFT commented

Azure Data factory - copy data from mysql to mssql json type issue

I am trying to migrate db from mysql to mssql via copy data in data factory. When the type of column is JSON, it's converted to Byte[] automatically and the data is converted to byte as well. Then, the data in mssql is byte, not string after migration. ![80818-screenshot-2021-03-24-at-100907-am.png][1] [1]: /answers/storage/attachments/80818-screenshot-2021-03-24-at-100907-am.png Can I change the type of source and destination before migration?

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried to duplicate the table and modify the column type from JSON to text first in mysql, then use copy data in data factory to migrate data successfully. But I will still want to know can I tackle the above problem in data factory.

Moreover, I discover another issue. I changed the JSON type to text, and the length of the text is around 72XXX. After migration, the report is success but only 48XXX can be moved to mssql. To find out the reason of this issue, I tried to insert value to mssql by raw sql method, all texts (72XXX) can be successfully stored, so the data loss issue is not the limitation of data type nvarchar in mssql. It's possibly there is data loss using copy data in data factory. Am I right? How to handle this data loss issue?

0 Votes 0 ·
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT edited

Hello @bennywong-0956,
Thanks for the ask and using the Microsoft Q&A platform .

JSON is not a supported data type for MYSQL in ADF , please do check out the support data types here . For the second part of the ask you you please share the JSON ( please obfusticate the actual data ) ?

.
Thanks
Himanshu
Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

bennywong-0956 avatar image
0 Votes"
bennywong-0956 answered HimanshuSinha-MSFT commented

Hello @HimanshuSinha-MSFT ,

Thanks for your answer.
For the first question, if ADF cannot support json type, then I should change the type in advance.
For the second question, I found that the data loss is due to the type TEXT in mysql not enough length to store the data. It will be fine when I use the type MEDIUMTEXT or LONGTEXT.

Thanks again to reply my question.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @bennywong-0956 ,

It was great to know that you were able to get to a resolution .

We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .

Thanks for sharing the knowledge on TEXT/MEDIUMTEXT /LONGTEXT , i was totally ignorant about that .

Thanks
Himanshu

0 Votes 0 ·