Stop ADF escaping character when creating JSON file from stored procedure

Question

I have a copy data activity that executes a stored procedure, which outputs a query into JSON, using FOR JSON path.

The copy activity then sinks the data to a .json file in a given folder location.

However, when reviewing the file, the double quotes are being escaped with a \ so my JSON isn't valid.

Here's an example;

CREATE PROCEDURE #testJson
AS
    SELECT 'Michael' AS Forename, 'Smith' AS Surname 
    FOR JSON PATH

EXEC #testJson

The output in SQL Server, is as follows;

[ { "Forename": "Michael", "Surname": "Smith" } ]

However, ADF outputs the following:

[{\"Forename\":\"Michael\",\"Surname\":\"Smith\"}]

How can I avoid this when using the copy data activity, as I need to execute a stored procedure to produce my JSON output.

Copy data source:

Sink:

JSON Source Code:

{
    "name": "Copy stored proc to JSON",
    "type": "Copy",
    "dependsOn": [],
    "policy": {
        "timeout": "0.12:00:00",
        "retry": 0,
        "retryIntervalInSeconds": 30,
        "secureOutput": false,
        "secureInput": false
    },
    "userProperties": [],
    "typeProperties": {
        "source": {
            "type": "AzureSqlSource",
            "sqlReaderStoredProcedureName": "#testJson",   
            "queryTimeout": "02:00:00",
            "partitionOption": "None"
        }, 
        "sink": {
            "type": "JsonSink",
            "storeSettings": {
                "type": "AzureBlobStorageWriteSettings"
            },
            "formatSettings": {
                "type": "JsonWriteSettings"
            }
        },
        "enableStaging": false
    },
    "inputs": [
        {
            "referenceName": "DestinationTables",
            "type": "DatasetReference",
            "parameters": {
                "TableSchema": "",
                "TableName": ""
            }
        }  
    ],
    "outputs": [  
        {
            "referenceName": "testJsonOutput",
            "type": "DatasetReference"
        }
    ]
}

Answer 1

• I have created the same stored procedure and executing it in my copy data activity source. The following is its configuration.

• However, take delimited text dataset instead of JSON dataset as suggested by @Ashwin Mohan. You can give the file extension as .json and the file written will be rendered as a JSON file. The following are the configurations of my sink delimited text dataset.

• The following would be the output file generated and you can see that the file will be rendered as a JSON file.