How to store set variable json array output into csv file in data factory

Mohit Bokade 0

I used the Lookup activity under for each loop and used Store Procedure which gave me some record as an output if the condition is fails

So that output I have stored into the Set Variable and then those values I need to store into csv file whose file name can be anything but needs to be created automatically.

After loading into csv it should look like
User's image

User's image

Under for each activity User's image

If Lookup output count greater or equal to 1
User's image

LookUp Value Output -

{
	"count": 25,
	"value": [
		{
			"id": 1,
			"claim_id": 123,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		},
		{
			"id": 2,
			"claim_id": 456,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		},
		{
			"id": 3,
			"claim_id": 789,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		},
		{
			"id": 4,
			"claim_id": 678,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		}
	],
	"effectiveIntegrationRuntime": "AutoResolveIntegrationRuntime (East US)",
	"durationInQueue": {
		"integrationRuntimeQueue": 1
	}
}

Set Variable Output -

{
	"name": "ErrorRecord",
	"value": [
		{
			"id": 1,
			"claim_id": 123,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		},
		{
			"id": 2,
			"claim_id": 456,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		},
		{
			"id": 3,
			"claim_id": 789,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		},
		{
			"id": 4,
			"claim_id": 678,
			"filename": "ErrorRecord1.csv",
			"IsDuplicated": true,
			"ReasoneOfError": null
		}
	]
}

Amira Bedhiafi 16,071 Reputation points

2024-04-27T13:15:20.9833333+00:00

Can you please clarify your problem ?
Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-04-29T06:47:51.2+00:00

@Mohit Bokade We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mohit Bokade 0 Reputation points

2024-04-29T10:25:50.0366667+00:00

Sure, here's a clearer version of your sentence:

"Hello, I've stored a value in a Set Variable activity as an array variable. Now, I want to take that array (which is in JSON format) and save it into a new CSV file. I'd like this file to be created in a specific blob storage container. Could you please assist me with this? Let me know if you need any further clarification."
Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-04-30T10:40:32.8233333+00:00
Hi @Mohit Bokade

To create a CSV file from the JSON array stored in the Set Variable activity and save it to a specific blob storage container, you can use the following steps:

Create a new pipeline in Azure Data Factory and add a Copy Data activity to it.

In the Source tab of the Copy Data activity, select the Set Variable activity as the source dataset.

In the Sink tab of the Copy Data activity, select the blob storage container where you want to create the CSV file as the sink dataset.

In the Mapping tab of the Copy Data activity, select the JSON format as the source data format and the CSV format as the sink data format.

In the Mapping tab, you can specify the column mappings between the source and sink datasets. Since your JSON array has the same structure as the CSV file you want to create, you can leave the column mappings as they are.

In the Sink tab, you can specify the name of the CSV file you want to create. You can use dynamic content to generate a unique file name for each run of the pipeline.

You can use the following expression to convert the JSON array to a CSV format:
@string(concat('id,claim_id,filename,IsDuplicated,ReasoneOfError', '\r\n', join(variables('ErrorRecord').value, ',') ))
This expression concatenates the column names with a new line character, and then joins the values of the JSON array with a comma separator.

Once you have configured the Copy Data activity, you can run the pipeline to create the CSV file in the specified blob storage container.

Here's an example of how the JSON array stored in the Set Variable activity can be mapped to a CSV file in the Copy Data activity:

{ "name": "CopyData", "type": "Copy", "inputs": [ { "referenceName": "SetVariableDataset", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "BlobStorageDataset", "type": "DatasetReference" } ], "typeProperties": { "source": { "type": "JsonSource", "storeSettings": { "type": "AzureBlobStorageReadSettings", "recursive": true } }, "sink": { "type": "DelimitedText

I hope this helps! Let me know if you have any further questions.
Mohit Bokade 0 Reputation points

2024-04-30T12:23:34.1933333+00:00

Hello @Smaran Thoomu , Thanks for your response but I did not find Set Variable activity as the source dataset while creating the dataset in copy activity
Could you please provide some screenshort and example that will easy to understand me

Thanks !
Smaran Thoomu 10,405 Reputation points Microsoft Vendor

2024-05-02T13:44:22.6766667+00:00
Hi @Mohit Bokade

You can also achieve your desired outcome, you can follow these steps:

Lookup Activity and Stored Procedure:

Use the Lookup activity within a For Each loop to retrieve records based on your condition.

Store the output of the Lookup activity into a Set Variable.

CSV File Creation:

Create a CSV file with an automatically generated filename.

The filename can be anything you choose.

CSV Content:

Populate the CSV file with the data from your Set Variable.

The CSV content should match the structure you provided:

[ { "id": 1, "claim_id": 123, "filename": "ErrorRecord1.csv", "IsDuplicated": true, "ReasoneOfError": null }, { "id": 2, "claim_id": 456, "filename": "ErrorRecord1.csv", "IsDuplicated": true, "ReasoneOfError": null }, { "id": 3, "claim_id": 789, "filename": "ErrorRecord1.csv", "IsDuplicated": true, "ReasoneOfError": null }, { "id": 4, "claim_id": 678, "filename": "ErrorRecord1.csv", "IsDuplicated": true, "ReasoneOfError": null } ]

You can create a CSV file with an automatically generated filename and populate it with the data from your Set Variable. The CSV content should match the provided JSON structure.

I hope this helps! Let me know if you have any further questions.

Share via

How to store set variable json array output into csv file in data factory