Data Factory Copy Data Source as Body of Sink Object

user_4347515 0
Viewed 541

I've been trying to create an ADF pipeline to move data from one of our databases into an azure storage folder - but I can't seem to get the transform to work correctly.

I'm using a Copy Data task and have the source and sink set up as datasets and data is flowing from one to the other, it's just the format that's bugging me.

In our Database we have a single field that contains a JSON object, this needs to be mapped into the sink object but doesn't have a Column name, it is simply the base object.

So for example the source looks like this A picture of the table

and my output needs to look like this

[
    { 
      "ID": 123, 
      "Field":"Hello World", 
      "AnotherField":"Foo"
    },
    { 
      "ID": 456, 
      "Field":"Don't Panic", 
      "AnotherField":"Bar"
    }
]

However, the Copy Data task seems to only seems to accept direct Source -> Sink mapping, and also is treating the SQL Server field as VARCHAR (which I suppose it is). So as a result I'm getting this out the other side

[
    {
      "Json": "{\"ID\": 123,\"Field\":\"Hello World\",\"AnotherField\":\"Foo\"}"
    },
    {
      "Json": "{\"ID\": 456,\"Field\":\"Don't Panic\",\"AnotherField\":\"Bar\"}"
    }
]

I've tried using the internal @json() parse function on the source field but this causes errors in the pipeline. I also can't get the sink to just map directly as an object inside the output array.

I have a feeling I just shouldn't be using Copy Data, or that Copy Data doesn't support the level of transformation I'm trying to do. Can anybody set me on the right path?

azure-data-factory-pipeline azure-data-factory azure json
1 Answers

Using a JSON dataset as a source in your data flow allows you to set five additional settings. These settings can be found under the JSON settings accordion in the Source Options tab. For Document Form setting, you can select one of Single document, Document per line and Array of documents types.

enter image description here

Select Document form as Array of documents.

Refer - https://docs.microsoft.com/en-us/azure/data-factory/format-json

user_111048050
Related