Forum Discussion
knoerregaard
Aug 17, 2023Copper Contributor
Azure Data Factory Copy Activity dynamic schema mapping issue
I am trying to do a dynamic schema mapping in Azure Data Factory but with little luck.
I am having trouble to cast or convert when sinking. the JSON_Query incapsulates the json as a string and is also escaping the double quotes. I need a clean JSON object to work with.
The Source SQL statement
SELECT [table] ,JSON_query(data_context) as data_context ,[operation] ,[updated_at] FROM [BIS_ChangedDataOnEventStage] where [id] <= 5000
the result
[ { "table": "pnt", "data_context": "{\"pnt_name\":\"P000_KLAUS22\",\"pnt_no\":136,\"}", "operation": "DEL", "updated_at": "2023-08-17T07:51:42.447" } ]
What I would like is the following result
[{ "table": "pnt", "data_context": { "pnt_name": "P000_KLAUS22", "pnt_no": 136 }, "operation": "DEL", "updated_at": "2023-08-17T07:51:42.447" }]
I have been trying al sorts of things with the translator object. My best guess is to explain ADF how to read the object therafter declaring sink properties.
{ "type": "TabularTranslator", "mappings": [ { "source": { "path": "context.$['pnt_name']", "type": "Object" }, "sink": { "path": "pnt_name", "type": "Object" } } ] }
Thanks in advance
No RepliesBe the first to reply