Azure Question posted in
The official documentation to get you started can be found here.

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.

Tags:

2

Answers


  1. 0

    You can use Stored procedure option inside lookup activity to get the output of the SP.

    To load the output result into a blob file in Azure blob storage, you can use a copy activity , point the source to a dummy file having a single column.

    Now use additional column option in source setting in copy activity and use the expression @{activity(‘lookup1’).output.value[0]}

    Go to mapping tab, and import schema, remove the existing dummy column and only keep the additional column in schema and execute the copy activity

    enter image description here

    enter image description here

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search