Copy Activity: How to iterate through Business Central API's JSON Response using Pagination?

Question

I am new to using Azure Data Factory and I am currently working on a project to copy data from Dynamics 365 Business Central API to Azure SQL Database. I have read both the docs on REST and OData connecters and because Business Central docs says its API is REST, I chose to go with the REST connector. What I am trying to do is copying data from my Company's Business Central Database into Azure SQL database. I read the Pagination support docs and it was stated that:

"When copying data from REST APIs, normally, the REST API limits its response payload size of a single request under a reasonable number; while to return large amount of data, it splits the result into multiple pages and requires callers to send consecutive requests to get next page of the result."

I am supposed to have 111 rows copied into an Azure SQL Table. I have made the mapping in the copy activity, chose the columns that I needed and specified the JSON Path of a nested JSON array for cross-apply (Collection Reference). but I when I run the pipeline only the first request is carried out. Example: The data I get from Business Central is like the following JSON Format (When I preview Data inside the ADF Copy Activity Source:

{"@Odata.context": "Our API URL", "value":[
{"@Odata.etag":"xxxxxxxxxxxx"
"id1":"xxxxxx"
"name1":"xxxxxx"
},
{"@Odata.etag":"xxxxxxxxxxxx"
"id2":"xxxxxx"
"name2":"xxxxxx"
}

and it goes like this, so value is an array that contains different entries. So based on this example I should copy two columns into the database and two rows. However, only the first row is copied (id1 and name1) then I get the "Pipeline successful". There is no iteration to the following entries (id2 and name2)

Answer 1

I have tried with some sample API using copy data activity and only 1 row got inserted among 10 records in my case.

Alternatively, I have tried with data flow activity and was able to load all the rows into the SQL database by flattening the source.

1. Connect source with your Rest API dataset. In source options, select the format and document form.

2. Add flatten transformation to flatten the source data by choosing the array data to flatten.

3. Connect sink to SQL database and map the source columns to sink.