Copy Activity: How to iterate through Busincess Central API using Pagination ?

Question

Copy Activity: How to iterate through Busincess Central API using Pagination ?

Osama Ahmed 131

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 set up all the steps and everything worked fine, but only the first entry is copied into the SQL database and the rest of the data is not copied. I tried using the "EndCondition" pagination rule put the pipeline keeps running for a long time, and I think I made a mistake and I set an infinite loop. If any one has encountered such issue, your help and guidance will be much appreciated.

MartinJaffer-MSFT 26,236 Reputation points

2022-05-31T19:17:51.647+00:00

Hello @Osama Ahmed and welcome to Microsoft Q&A.

As I understand, you are trying to use Data Factory to pull from Dynamics Buisness Central to SQL. It looks like you are using the REST connector, as you mention EndCondition problems.

I am not familiar with Buisness Central, so I am looking in their documentation for examples of their pagination. So far I haven't found any, but I have found many mentions of OData. Data Factory has an OData connector, and is worth trying that out.

However one of the main pages also says it is REST.

Can you describe or share an example of the condition you want to use? What the returned pagination looks like?
Osama Ahmed 131 Reputation points

2022-06-01T02:36:20.557+00:00

Hello Martin,

Thank you for reply. I have read both the docs on REST and OData connecters and because one of the main pages also says it 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.

An example would be Example 4: Pagination Support
Osama Ahmed 131 Reputation points

2022-06-01T06:48:00.72+00:00

The data I get from Business Central is like the following JSON Format:
{"@Odata.context": "Our API URL", "value":[
{"@Odata.etag":"xxxxxxxxxxxx"
"id1":"xxxxxx"
"name":"xxxxxx"
},
{"@Odata.etag":"xxxxxxxxxxxx"
"id2":"xxxxxx"
"name":"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 then I get the "Pipeline successful", but it is supposed to iterate until all the data is copied

Accepted answer

0 additional answers

Your answer

MartinJaffer-MSFT 26,236 Reputation points

2022-05-31T19:17:51.647+00:00

Hello @Osama Ahmed and welcome to Microsoft Q&A.

As I understand, you are trying to use Data Factory to pull from Dynamics Buisness Central to SQL. It looks like you are using the REST connector, as you mention EndCondition problems.

I am not familiar with Buisness Central, so I am looking in their documentation for examples of their pagination. So far I haven't found any, but I have found many mentions of OData. Data Factory has an OData connector, and is worth trying that out.

However one of the main pages also says it is REST.

Can you describe or share an example of the condition you want to use? What the returned pagination looks like?
Osama Ahmed 131 Reputation points

2022-06-01T02:36:20.557+00:00

Hello Martin,

Thank you for reply. I have read both the docs on REST and OData connecters and because one of the main pages also says it 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.

An example would be Example 4: Pagination Support
Osama Ahmed 131 Reputation points

2022-06-01T06:48:00.72+00:00

The data I get from Business Central is like the following JSON Format:
{"@Odata.context": "Our API URL", "value":[
{"@Odata.etag":"xxxxxxxxxxxx"
"id1":"xxxxxx"
"name":"xxxxxx"
},
{"@Odata.etag":"xxxxxxxxxxxx"
"id2":"xxxxxx"
"name":"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 then I get the "Pipeline successful", but it is supposed to iterate until all the data is copied

Answer 1

MartinJaffer-MSFT 26,236

Oh I see, so this isn't about problems with paging, this is about getting all the data in a single page.

Several things to check. First of all, in your mapping, is there an array index like $.value[0].name ? Like I show in the image below:

This would cause only the first record's properties to be recorded. The [0] means get the item 0 places from the top.

Instead you would need to use the Collection reference. In your case that would be set to $.['value'] . The column mapping would also need to be changed, and not start with $. Again, image below. I hope I remember the format right. I think it was just the property name, or ['property name'].

Osama Ahmed 131 Reputation points

2022-06-01T17:23:25.22+00:00

Yes that is exactly the problem and now I can copy all the data. Thank you very much
MartinJaffer-MSFT 26,236 Reputation points

2022-06-01T18:27:12.437+00:00
Thanks for letting me know. I have converted it to answer, so you may mark as accepted answer, @Osama Ahmed .

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
Osama Ahmed 131 Reputation points

2022-06-02T17:00:19.577+00:00

Thank you for your help @MartinJaffer-MSFT . I have a further question regarding this. In case I have a nested array inside the main array, how the data copy cane be performed then?

For example:
collection reference: $[value]
Mapping column:
$['value'][0]['data'][0]['ID']
$['value'][0]['data'][0]['Name']
$['value'][0]['data'][0]['Email']
Now I want to copy the data inside ['data'] which is nested within ['value']
MartinJaffer-MSFT 26,236 Reputation points

2022-06-03T16:22:05.393+00:00

Unfortunately, copy activity only flattens one nest at a time. You could do multiple copy activities, or you could use a single Data Flow. Data Flow is meant for transformations like this. @Osama Ahmed

Share via

Copy Activity: How to iterate through Busincess Central API using Pagination ?

0 additional answers

Your answer