How to create a Json file and save it to a storage account using Azure Data Factory

Question

I'm new to Azure Data Factory and have a requirement that sounds pretty simple but I not able to figure it out.

I have a Json file saved in a storage acount container, from which i need to read the value of "LastRunDate" (pretty easy).

{
    "LastRunDate": "2024-01-12"
}

The problem is, there is a senarion where this file might not exist. If the file does not exist while trying to read it, i need to create the file on the fly and write the below default json to it.

{
    "LastRunDate": "2000-01-01"
}

My approach was to use the "Get MetaData" Activity and use the metadata "exist" to find out if the file exist or not - which was fine. My thought then was to assign a variable (with the default Json string) and write the value of the variable to the new file. This is the point where i hit a brick wall. Im not able to create the json file on the fly with a default value.

NOTE: Not in the position to create any dummy files upfront to copy from. The file has to be created on the fly.

Answer 1

The simplest solution:

Create the default file manually in a different folder on the storage account (once)

In your pipeline:

1. use metadata activity to check the content of the main folder
2. use if activity to check if the main file is missing
3. use a copy data activity to copy the default file to the main location

Answer 2

You can also try below approach to create JSON file from ADF variable.

First take a dummy csv file in a temporary storage location with only one row data like below.

Create a dataset for this and uncheck the First row as header in the dataset settings.

Now, create another csv dataset with the storage location to the required JSON file.

Here, we are creating a new JSON file myfile.json using csv dataset, so give the below configurations in the csv dataset.

Now, after checking whether the file exists or not with Get meta data activity, take an if activity and give the output from Get meta data exists to this if activity expression.

Here, I have given the below expression.

@not(activity('Get Metadata1').output.exists)

In the True activities of if, we are going to create new JSON file with defaut JSON. In the False activities of if activity, you can go ahead with your activities.

First give your JSON as a string in a string variable using set variable activity.

@string('{
"LastRunDate": "2000-01-01"
    }')

In copy activity, take the dummy dataset as source and the another csv dataset as sink.

In the source, create an additional column by clicking on new and give the column name (Here column name is new) and give the above string variable in the dynamic content like below.

Give the below expression as dynamic content for the mapping of the copy activity.

@json('{
                                    "type": "TabularTranslator",
                                    "mappings": [
                                        {
                                            "source": {
                                                "name": "new",
                                                "type": "String"
                                            },
                                            "sink": {
                                                "type": "String",
                                                "physicalType": "String",
                                                "ordinal": 1
                                            }
                                        }
                                    ],
                                    "typeConversion": true,
                                    "typeConversionSettings": {
                                        "allowDataTruncation": true,
                                        "treatBooleanAsNumber": false
                                    }
                                }')

This mapping ensures to copy only the additional column that we created in the source.

Debug the pipeline, and if there is no JSON file in your storage location, your JSON file with required JSON will be created like below.

If your requirement is only to create a default JSON, then the above answer by @Chen Hirsh is a better option.

But if you want to create a dynamic JSON from ADF, then you can follow this approach because in this approach you can build whatever JSON that you want dynamically in the set variable and give it to copy activity.