jsonazureazure-data-factoryazure-blob-storagepipeline

Azure Data Factory pipeline save SQL stored procedure first row result value (JSON string) to blob storage

I'm using a stored procedure to query SQL database tables and the result is a JSON string. I would like to store the string in an Azure storage blob but can't seem to figure out how to just store the SProc result value.

The stored blob data has the following... I just want the JSON value (red arrow part):

enter image description here

I can't seem to determine how to specify just the .value from the result set. The preview data only includes the value.

I'm also trying to use values in a for each loop to specify the name of the blob.

Solution

  • In this case, instead of lookup or stored procedure activity, you can achieve your requirement by using a single copy activity.

    Create a SQL table dataset without giving any table in the dataset and give this as source in the copy activity. In the dataset also, don't import any schema.

    enter image description here

    For the sink, create a delimited text dataset with below configurations. This will create JSON file in the target location.

    enter image description here

    Don't import any mapping in any datasets and in the copy activity mapping as well.

    Now, run the pipeline and it will create the desired JSON file.

    enter image description here

    To use this JSON file further in ADF, you need to create a JSON dataset and use it as per your requirement.