Copy Activity (Mapping): How to iterate through Busincess Central API using Azure Data Factory REST Connector

Question

Hello Everyone,

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

Hi @Osama Ahmed ,

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

As per my understanding about your query, It seems you are trying to copy data from Dynamics 365 API to Az SQL DB , for that you are using copy data activity in ADF pipeline and in the mapping you have enabled the collection reference for value[] array, however, after pipeline execution , it is just copying the first item of the array which is not expected. Please correct me if my understanding is wrong.

The Json that you have shared is having different key names in different items of the array, which is why collection reference is not working properly in your case. Kindly ask the source team to provide same keynames in all the items in order to process the data from the API.

This is how your mapping is probably looking like right now:

This is how it should look:

Kindly let us know if it helps! Thanks!