azureazure-data-factoryetlazure-data-lake

How to transfer "Set Variable" activity output into Json file using "Copy Data" activity or any other options?

I have tried "Copy Data" activity to copy data from Set Variable output into a JSON file, but it's not working. Please check below the steps I have tried:

Set Variable Activity: Here the variable type is Array which contains this data below:

{
    "name": "OrderList",
    "value": [
        {
            "id": 3,
            "portfolioId": 92,
            "createDate": "2023-10-17T14:17:35.45Z",
            "externalId": null,
            "quantity": 10,
            "type": {
                "name": "Buy"
            }
        },
        {
            "id": 4,
            "portfolioId": 120,
            "createDate": "2024-08-27T05:32:50.546Z",
            "externalId": null,
            "type": {
                "name": "Buy"
            }
        }
    ]
}

Set Variable

Copy Data Activity - Source: I have taken an empty JSON dataset and added an Additional Column

Source

Sink: I have taken an dummy JSON to sink which contains empty "OrderList"

Sink

Mapping: Import schemas couldn't maapped automatically, that's why I have added the additional column manually

Mapping

After doing all of this I am getting below error:

Failed to convert the value in 'value' property to 'System.String' type

Can anyone suggest what I have missed. Thanks in advance.

Solution

  • Failed to convert the value in 'value' property to 'System.String' type

    The cause of error is the additional column value should be in string format so to store it in Json file, when you store it with string format in Json file it will add backslashes as escaping characters. To resolve this and add array value in Json file you need to follow below steps:

    @concat('{"OrderList" : ',string(variables('OrderList')),'}')

    enter image description here

    enter image description here

    Output:

    enter image description here