How to insert json object in single Column in Azure Synapse SQL Table through Azure Datafactory?

Question

How to insert json object in single Column in Azure Synapse SQL Table through Azure Datafactory?

KUMAR Abhishek 51

I have a requirement to insert JSON object from json file into single Column in Table of Azure Synapse SQL,

I have tried using Copy activity in data factory but I'm getting below error:

{
"errorCode": "2200",
"message": "Failure happened on 'Sink' side. ErrorCode=JsonUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JObject with value {\"Offer\":\"CentOS\",\"ServiceType\":\"Standard_A1_v2\",\"xxxx\":\"\",\"Publisher\":\"OpenLogic\",\"Sku\":\"7.5\",\"Meterid\":\"xxxxxxxxxxx\",\"ImageType\":\"\"} is not supported yet. Please either remove the targeted column 'Personalized' or enable skip incompatible row to skip the issue rows.,Source=Microsoft.DataTransfer.Common,'",
"failureType": "UserError",
"target": "Copy data1",
"details": []
}

Can you suggest me if it is possible through Azure Data Factory ?

@KranthiPakala-MSFT

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-07-22T01:25:55.437+00:00

HI @KUMAR Abhishek ,

Thanks for your query. I'm looking into it and will get back to you as soon as I have an update on this. In the meantime, would you please share sample source data and sink structure, which would help us to reproduce this scenario?

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-07-22T01:25:55.437+00:00

HI @KUMAR Abhishek ,

Thanks for your query. I'm looking into it and will get back to you as soon as I have an update on this. In the meantime, would you please share sample source data and sink structure, which would help us to reproduce this scenario?

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi @KUMAR Abhishek ,

Could you please try enabling Map Complex values to string under Mapping section of Copy activity and try to map the complex column to the desired destination column.

This should help to resolve the issue. Please let us know how it goes.

----------

Thank you
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

KUMAR Abhishek 51 Reputation points

2020-07-22T07:04:20.957+00:00

@karishmatiwari-msft

I have enabled this also, but getting the same error. have you tried the same which I'm trying in copy activity ?

in the screenshot, I think it is the same but it is not working.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-07-25T00:26:38.87+00:00
Thanks for your response @KUMAR Abhishek . I would like to do a test with dummy data similar to your JSON structure, would it be possible for you to provide a dummy JSON source and sink structure so that I can try to reproduce the issue? If so, please feel free to email at AzCommunity@Microsoft.com with below details.

Subject of the email: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread>

Dummy source file and sink structure

Please let me know here once you have done the same

Thank you.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2020-07-28T22:35:34.76+00:00

Hi @KUMAR Abhishek ,

Just following up to see if you have got a chance to share the details requested in my previous comment? In case if you have a resolution yourself, please do consider to share it here with community.

Thank you.
KranthiP 1 Reputation point

2020-07-29T23:58:37.337+00:00

Hi @KUMAR Abhishek ,

Just checking to see if your issue was resolved? In case if you have found a different solution, please do consider to share it here with community, as it would be helpful for others reading this thread. And if you still need assistance, please do confirm the details as requested in my previous comments.

Thanks.
GB 1 Reputation point

2020-10-01T18:35:30.147+00:00

Desperately need help on this issue please.

I am having the exact same error and I am unable to copy nested JSON arrays to a Synapse SQL Data Pool.

Share via

How to insert json object in single Column in Azure Synapse SQL Table through Azure Datafactory?

1 answer

Your answer