CODEWITHSUNDEEP
azureazure-data-factoryetlazure-data-lake
Ruhul Amin
Ruhul Amin

Reputation: 27

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.

Upvotes: 0

Views: 110

Answers (1)

Pratik Lad
Pratik Lad

Reputation: 8402

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:

  • In source add additional column value as below
@concat('{"OrderList" : ',string(variables('OrderList')),'}')

enter image description here

  • For sink create dataset with CSV format with below settings:

enter image description here

  • Add this to your sink and in mapping delete all unwanted column and only keep the additional column.

Output:

enter image description here

Upvotes: 1

Related Questions