How to move data from response of Http to a JSON file using Azure Data Factory

Question

I am trying to move data from Http Response (JSON format) --> a JSON file.
I am not moving data directly from REST API, but from Azure Function's Response (in JSON format).

Initially, I had three parts in this pipeline (built this pipe line based on this YouTube);
I was trying to mimic this pipeline by following each step on this video, but I am stuck how to move data from text (csv) file that has JSON data to SQL.

1. Azure Function (to get Response of Http)
2. Variable
3. Copy data (text file --> text file)
   The output of 3rd step produced JSON data but in a text file, so I am not able to map data into my final destination, which is SQL table.
   

------------------------------------------------------------------------------------------------------

(1st step)

(2nd step)
To explain how I built the last pipeline, regards to detail of 2nd step, Variable, I used "output.Response".

(3rd step)
To explain how I built the last pipeline, regards to the 3rd step (move data from txt file to txt file), I used additional column like below.

Now, I am trying to find a best way to move data from Response of Http --> JSON file --> (ultimately) SQL table.

How do I move data from Azure Function's Output.Response to a JSON file?

Thanks.

Answer 1

Hi @KingJava ,
Thanks for posting your query on Microsoft Q&A platform.
Glad that you found out the solution. As you mentioned, post copying the data into Delimited text file(csv format) , you need to add a copy activity with source as a file with JSON format pointing to the same Delimited text file(csv format) as is used in first copy activity in sink dataset. You can use auto create table and go with default mapping as well. Explicit mapping for JSON file to SQL table is not mandatory. You have already got the solution, still attaching the screenshot for additional help for the community.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 2

I was able to find the solution by having the data type of JSON on next "Copy data" pointing to the text file (csv) that I used. Even though the file was originally created with DelimitedText (csv) file, when I pointed that same file, but using JSON format, it took it. And rest (mapping to SQL table) is easy.

Answer 3

Hey ,

Can we post data from CSV(Json) file to rest api using data factory . If it is possible let me help to understand the approach .

No solution being found on anywhere on internet , even we have seen option to use REST API as SINK in data factory pipeline .