How to save complete web activity output to ADLS Gen2 in JSON format? Azure Synapse Studio

Question

I want to save the output of my Web activity (which hits an Api and then fetches data from that Api in Json format) into blob storage account container. Also, my web activity is within a foreach - so multiple Api links are being hit and multiple files need to be created to store the fetched data. The final data that is being stored in Blob storage also needs to be in Json format.

Answer 1

You can follow these steps to achieve your goal :

1. Web Activity Setup:
   • Configure your Web activity to call the API and capture the output in JSON format.
   • In the Settings tab, set Secure Output to true if your API response contains sensitive data.
2. Capture Web Activity Output:
   • Ensure that the Web activity captures the response in the @activity('WebActivityName').output format.
3. Foreach Activity Setup:
   • Use a Foreach activity to iterate over a list of API endpoints or parameters.
   • Within the foreach loop, use the Web activity to make API calls for each iteration.
4. Copy Data Activity:
   • Inside the foreach loop, after each Web activity, use a Copy Data activity to save the output to ADLS Gen2.
   • In the Source tab of the Copy Data activity:
     • Select Dataset as REST or HTTP (depending on your API source).
     • Set the Request body to capture the Web activity output dynamically using the expression:

     
             @activity('WebActivityName').output
     
     

5. Sink (ADLS Gen2):
   • In the Sink tab of the Copy Data activity:
     • Select a Blob dataset pointing to your ADLS Gen2.
     • Set the File Path dynamically, so that each API response is saved as a unique file in your Blob container. You can use expressions such as:

     
             container/foldername/@{item().parameter}.json
     
     

     • Ensure that File format is set to JSON.
6. Configure File Path for Multiple Files:
   • If you want each API response to be saved as a separate file, dynamically define the filename using the loop variable or a unique identifier in the API response.
   • For example, use the iteration index to generate different file names like:

   
       container/foldername/output_@{item().index}.json
   
   

7. Error Handling (Optional):
   • Add error handling within your foreach loop to ensure that failed API calls are captured and do not disrupt the process.

Answer 2

Hi @Mitashi Malkani

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

To save the output of your Web activity into a Blob storage account container in JSON format, you can use the following steps:

I tried to repro the scenario and used .csv file format instead of JSON. You can refer and change the format as required.

• Create a variable and use set variable activity after the Web activity and use this expression: @{activity('Web1').output.value[0]} in the value of set Variable.
• Use copy activity and have a dummy .csv file with one column as the source dataset . Add additional column and provide a new column named WebActvityOutput with value = @{variables('var1')} . In the sink , use the same dataset. In the mapping tab , click on Import schema and delete the column from dummy file and only keep the additionally added column.
• Execute the pipeline and check if the webActivity output got stored in the .csv in the ADLS.

Hope this helps. Do let us know if you have any further queries.

---

If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.