Rest API post request file creation

Question

Rest API post request file creation

Priya Jha 896

Hi All,

Currently i am leveraging Rest API as a source and blob as a sink in Azure Data Factory. The Rest API output is triggered on post request

Preview Data:

Now when i am importing the mappings and executing the job only 1st row is getting copied in the file.

How do i ensure that all the rows of the rest API get coped into the file.

2 answers

Your answer

Answer 1

Nandan Hegde 34,941 MVP

Based on the output, it seems to be Object type.
Not sure whether the approach is correct but you can map it to array based on something like below :

activity.output.results[0].tables[0].rows
and then use that to load into file

@AnnuKumari-MSFT @ShaikMaheer-MSFT : any thoughts ? As this might be a haphazard way :(

Answer 2

AnnuKumari-MSFT 34,451 Microsoft Employee

Hi @Priya Jha ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, you are trying to copy data from REST API to ADLS file. However, the results are not coming as expected. Kindly let me know if my understanding is incorrect.

Since the source data is of hierarchical type. You need to use 'row[]' array as collection reference in copy activity , based on which iterations can be made and data can be dynamically fetched.

For more details, kindly check the below resources:
Hierarchical source to tabular sink
How to load Tabular and Hierarchical JSON data from Rest API to SQL Table

If this doesn't help in your case, you need to go for Mapping data flow where we have the capability to use Flatten transformation that can easily perform flatten over complex data.

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

Priya Jha 896 Reputation points

2022-10-12T09:33:52.887+00:00

Hi @AnnuKumari-MSFT

We want to avoid using DataFlows in our project. Is there a way to achieve this without using DataFlow as some what stated by @Nandan Hegde above.

On a side note, i tried leveraging rest API as source in DataFlow with the below configurations,

And source options similar to the configurations stated in the original question with format as None. Getting below error:

So what am i doing wrong? How to resolve this issue.
Priya Jha 896 Reputation points

2022-10-12T09:35:32.067+00:00

249701-dataflowerror.txt

Error
AnnuKumari-MSFT 34,451 Reputation points Microsoft Employee

2022-10-12T10:12:27.663+00:00

Hi @Priya Jha ,
Did you try selecting row[] in collection reference dropdown and then try mapping the columns in copy activity?
Priya Jha 896 Reputation points

2022-10-12T11:17:24.69+00:00

Hi @AnnuKumari-MSFT

In normal copy activity i am unable to see any value in collection reference w.r.t row[]

Attaching below is the output of the Rest API via web activity, the same API which i am trying to leverage in copy activity as a source.

249696-dummy.txt
AnnuKumari-MSFT 34,451 Reputation points Microsoft Employee

2022-10-12T12:08:19.113+00:00

Hi @Priya Jha ,
Thankyou for sharing the source file. Unfortunately, it's not possible to achieve your requirement via copy activity . You need to go for mapping dataflow as it's nested JSON in your case that needs to be flattened. Kindly use dataflow to achieve your required output.

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

Hope it helps. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well.
Priya Jha 896 Reputation points

2022-10-12T12:15:06.293+00:00

Hi @AnnuKumari-MSFT

Tried using DataFlow and am receiving this error:

249699-dataflowerror.txt
AnnuKumari-MSFT 34,451 Reputation points Microsoft Employee

2022-10-12T14:44:32.68+00:00

Hi @Priya Jha ,

I tried to flatten the source data using mapping dataflow, and it is working fine for me . Please have a look at the below images:

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

If the above answer helped, Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well
Nandan Hegde 34,941 Reputation points MVP

2022-10-12T14:54:32.09+00:00

Hey @AnnuKumari-MSFT
The error message shared by Priya is w.r.t REST API as a source and not the JSON file.
Based on the below details:

Should se add the headers here in dataflow rather than using the format as JSON.
The error message seems to be more w.r.t some data parsing wherein she is not able to preview the REST API output in dataflow source step itself but for the same config she is able to see in copy activity. Theses would be my thoughts on the above discussion thread
Priya Jha 896 Reputation points

2022-10-13T12:55:26.4+00:00

@AnnuKumari-MSFT

What @Nandan Hegde mentioned above is correct, i.e. when i am querying the Rest API via copy activity or lookup activity it is working fine but with the same configurations in data flow as source i am getting the above error while doing data preview and am not able to proceed.

Can you both help me in resolving this.

Share via

Rest API post request file creation

2 answers

Your answer