Azure Data Factory Copy Activity error mapping JSON to SQL

Question

I have an Azure Data Factory Copy Activity that is using a REST request to elastic search as the Source and attempting to map the response to a SQL table as the Sink. Everything works fine except when it attempts to map the data field that contains the dynamic JSON. I get the following error:

{ "errorCode": "2200", "message": "ErrorCode=UserErrorUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JObject with value {\"name\":\"department\"} is not supported yet, please either remove the targeted column or enable skip incompatible row to skip them.,Source=Microsoft.DataTransfer.Common,'", "failureType": "UserError", "target": "CopyContents_Paged", "details": [] }

Here's an example of my mapping configuration:

    "type": "TabularTranslator",
    "mappings": [
        {
            "source": {
                "path": "['_source']['id']"
            },
            "sink": {
                "name": "ContentItemId",
                "type": "String"
            }
        },
        {
            "source": {
                "path": "['_source']['status']"
            },
            "sink": {
                "name": "Status",
                "type": "Int32"
            }
        },
        {
            "source": {
                "path": "['_source']['data']"
            },
            "sink": {
                "name": "Data",
                "type": "String"
            }
        }
    ],
    "collectionReference": "$['hits']['hits']"
}

The JSON in the data object is dynamic so I'm unable to do an explicit mapping for the nested fields within it. That's why I'm trying to just store the entire JSON object under data in a column of a SQL table.

How can I adjust my mapping configuration to allow this to work properly?

kwyss88 · Accepted Answer · 2019-12-10 19:07:12Z

12

I posted this question on the MSDN forums and I was told that if you are using a tabular sink you can set this option "mapComplexValuesToString": true and it should allow complex JSON properties to get mapped correctly. This resolved my ADF copy activity issue.

answered Dec 10, 2019 at 19:07

kwyss88

1711 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ptfaferreira · Accepted Answer · 2019-12-04 23:16:26Z

0

I have the same problem a few days ago. You need to convert your JSON object to a Json String. It will solve your mapping problem (UserErrorUnsupportedHierarchicalComplexValue).

Try it and tell me if also resolves your error.

answered Dec 4, 2019 at 23:16

ptfaferreira

5926 silver badges22 bronze badges

11 Comments

kwyss88 Over a year ago

Are you saying to add some kind of conversion to the mapping configuration?

ptfaferreira Over a year ago

Yes, don't know the type of language that you are using to collect the data from the source. But for example if you are using c# can do something like this: string jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(jsonObject)

ptfaferreira Over a year ago

Or you can to a direct cast in the mapping configuration as in this example: stackoverflow.com/questions/49732768/…

kwyss88 Over a year ago

The source is just a POST elastic search query. I don't think you are able to do any kind of conversion when querying elastic search. The only valid Content-Type header is application/json. So I'm not sure a conversion like that is possible with this case.

kwyss88 Over a year ago

Unfortunately, you can't do a direct cast in the path field. It errors if you try to do something like "source": { "path": "@string(['_source']['data'])" }

|

Collectives™ on Stack Overflow

Azure Data Factory Copy Activity error mapping JSON to SQL

2 Answers 2

Comments

11 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

11 Comments

Linked

Related