Rest API End Condition in Copy Activity is not working

Philipp Kempter 56

I'm working on a paginated get requestin adf. As soon as an EndCondtion is set, the request does not work anymore.

I'm using pagination rules in order to loop the request until all data is loaded. The range rule works fine but as soon as I add the EndCondtion the following error message appears:
ErrorCode=UserErrorMoreThanOneObjectsReturned,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=More than one objects returned\r\n

The empty response from the api are just two squere bracket: []. Is there a way to set the square bracket as endcondtion?

Thanks
Philipp

MartinJaffer-MSFT 26,036 Reputation points

2022-05-31T18:50:32.69+00:00

Hello @Philipp Kempter and welcome to Microsoft Q&A.

I see you are having trouble with pagination, specifically the end condition.

At first glance, I see 2 things out of the ordinary.

First you are using 2 pagination rules at the same time. The range and the end condition. Is it the case sometimes the results stop sooner than expected, necessitating both rules?

The other is an incomplete JSON reference. You used $. I expected it to throw and error about that. $ or $.someProperty or $[0] would make more sense.
Specifically, the . in $. is the member operator, used to point to a named property, but you didn't name anything.

I am 80% confident in the following:

If you data is of the form
[
{
data1
},
{
data2
}
]

then end condition of $ , Empty should work.
Also
$[0] , Not Exist
Philipp Kempter 56 Reputation points

2022-06-01T05:23:36.67+00:00

Hi Martin

Thank you very much for your response - much appreciated!

Concerning your first point. I attempted the range + end condition as described in the link below. The goal is to set the end range to infinity and stop when there is no more data coming in. As soon as the end condition would be working I'd switch to "RANGE:0::2000"

https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#pagination-support

I tried both of the recommended end conditions, but both produced the same error. Do you have any other suggestions for me?
Eleanor Page 11 Reputation points

2022-06-24T11:28:30.387+00:00

Hello, I've been struggling with the same issue. The API I'm working with returns an array of JSON objects and I was getting the same error with the EndCondition rule set to $ Empty: 'More than one objects returned'. I re-read the pagination documentation and spotted this section, which suggests it's not possible to use pagination rules if the response body has more than one JSON object.

Supported values in pagination rules:
Value
A JSONPath expression starting with "$" (representing the root of the response body)
Description
The response body should contain only one JSON object. The JSONPath expression should return a single primitive value, which will be used to issue next request.
(https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory#pagination-support)
Abhinav Gundapaneni 56 Reputation points Microsoft Employee

2022-06-27T12:45:21.343+00:00

@MartinJaffer-MSFT Is there any workaround for the above issue? I'm also looking for the same solution. My API is returning multiple JSON objects and the Copy Data activity is giving the same error as mentioned above error - ErrorCode = UserErrorMoreThanOneObjectsReturned and all my data is being displayed on the error message.

Is there any workaround to put the end condition based on the count of the objects returned by the response?
Ronald Mussche 36 Reputation points

2022-07-13T08:05:01.71+00:00

Also struggling with this issue and cannot find a proper solution. I created an Idea on the feedback board:

f87b7576-f801-ed11-a81b-6045bd8606d4

If someone has a proper solution I am interested or please vote for a fix to get the attention.
Scott Faculak 6 Reputation points

2023-01-26T10:43:40.5+00:00

@MartinJaffer-MSFT we have tried both options $ Empty and $[0] NonExists with no luck, both result in "ErrorCode=RestMoreThanOneObjectsReturned". The headers returned do not offer any help as also mentioned in the accepted answer. So we are looking for alternative solutions where we only have the empty [] set returned and no option of other meta data.

Here is a result with data...

Any other suggestions?

Thanks in advance,

Scott
Janne Kujanpää 181 Reputation points

2023-11-17T15:14:54.59+00:00

I have exactly same problem than Scott Faculak described.

Is pagination actually supported by MSFT or not?

1 answer

Ellie Coder 41 Reputation points

2022-07-14T03:37:36.17+00:00

I have found a workaround for this which may help others. It uses one of the headers to determine the end condition. I used Postman to view the headers of my API call on the first empty page after all records had been returned. I found a header called Content-Length which equals 2 when no data is being returned. The 2 represents the body '[]' in my case. I then set the pagination rules per the instructions in example 4.6 on this page: https://learn.microsoft.com/en-us/azure/data-factory/connector-rest?tabs=data-factory The settings I entered where:
Name (1st box): End Condition
Name (2nd box): headers.Content-Length
Value (1st box): Const
Value (2nd box): 2.

The json for it looks like this:
"paginationRules": {
"AbsoluteUrl.{Page}": "RANGE:1::1",
"EndCondition:headers.Content-Length": "Const:2"
}
Please sign in to rate this answer.

8 people found this answer helpful.
Ronald Mussche 36 Reputation points

2022-07-14T06:03:19.85+00:00

Thanks, That is only working when the API returns headers. In my case the API only returns "[]"

when no empty page
[
{row1},
{row2}
]

there are no headers

Edit,
It is indeed in the headers not in the body. Will try this thanks!

Rick Slingwine 5 Reputation points

2023-01-25T16:40:16.8333333+00:00

Ellie Coder: Fixed my issue, thank you VERY much. Fought with this for 2 days trying to figure out a proper end condition.

Corbin Bohne 5 Reputation points

2023-02-27T16:13:21.0233333+00:00

This actually works! Great workaround. If anyone is confused (like I was) and is using Postman but doesn't see headers, make sure to toggle the response pane from "Body" to "Headers".

rajkiran boggala 0 Reputation points

2023-09-07T14:56:39.4066667+00:00

@Ellie Coder wow! that's a great workaround. It works perfectly for json objects with [] as the output! Thank you so much! I was stuck with this issue for over two days.

rajkiran boggala 0 Reputation points

2023-09-07T14:58:09.0166667+00:00

@Corbin Bohne , good example - yes the headers length would be two for [] output. Otherwise, it would be >2.

rajkiran boggala 0 Reputation points

2023-09-07T14:59:30.4066667+00:00

@Ronald Mussche I am not sure why it did not work for you! it works if the output is exactly [] unless it is coming as some json property name like data or results! In that case you may have use $. method.

Guven Tum 0 Reputation points

2024-02-29T13:41:29.0166667+00:00

Amazing. Works like a charm. Thank you.

Gerardo Arevalo 0 Reputation points

2024-05-07T03:04:31.4366667+00:00

Thanks for sharing. It worked perfectly using the header EndCondition that you found.
Sign in to comment

Share via

Rest API End Condition in Copy Activity is not working

1 answer