How can I convert string output of Set Variable activity to .csv or .txt file in Azure Data Factory

Question

I want to take the output of the Set Variable activity which is in String format and convert it into a .csv or .txt format.

[Set Variable output: 
{
    "name": "Data_String",
    "value": "[{\"Name\":\"Balaji\",\"EnabledFlag\":\"Y\",\"CategoryId\":\"300000290956003\",\"LastUpdateDate\":\"19-09-2023\",\"CreationDate\":\"19-09-2023\",\"Entity\":\"027\"},{\"Name\":\"Ahmed\",\"EnabledFlag\":\"Y\",\"CategoryId\":\"300000290956073\",\"LastUpdateDate\":\"19-09-2023\",\"CreationDate\":\"19-09-2023\",\"Entity\":\"027\"},

}
]

Here the output of the Set Variable is stored in 'Data_String' and now want to convert this string output data to .csv or .txt format and store it in a Blob storage.

What can I do to achieve this..?

Which activity do I need to add after the Set Variable activity and what do I need to configure in that activity?

Answer 1

Your variable value is a JSON string. To store it in a CSV format, first you need to create a correct JSON file from it. Laten Copy that JSON file to CSV file using copy activity.

Follow the approach in this SO Answer to create a JSON file from the JSON string using copy activity.

It will create the JSON file like below.

After creating the JSON file, use another copy activity with a new JSON dataset(pointing to this JSON file) as source and a new CSV dataset (with default configurations) as sink.

Now, execute this again and the required CSV file will be created like below.