REST Pagination in a Data Flow

Question

REST Pagination in a Data Flow

ralphjjr 101

I'm trying to get pagination working in a data flow. It works great in a copy activity.
These are my settings:

When I try the same thing in a Data Flow. It just doesn't work. I've tried a bunch of variations and can't seem to get it to actually paginate. It will only ever get the first page.

I've tried many variations of values. The short explanation is, it's a Query Parameter called "starting_after" and it should get the value of body.data[99].id
In the Data Flow I did already notice that arrays start at 1, and not at 0, so I have data[100].id - but it doesn't even paginate, so that doesnt seem to be the issue. It only ever gets one page. I even adjusted Request Interval, to see if perhaps that somehow would get it going.

What am I missing?

Here is an example of the response.

{  
    "object": "list",  
    "data": [  
        {  
            "id": "evt_1",  
            "object": "event",  
            "api_version": "2018-02-28",  
            "created": 1642505475,  
            "data": {  
                "object": {  
                    "id": "sub_1",  
                    "object": "subscription",  
                    "application_fee_percent": null  
                },  
                "previous_attributes": {  
                    "current_period_end": 1642435365,  
                    "current_period_start": 1637856965  
                }  
            },  
            "livemode": false,  
            "pending_webhooks": 0,  
            "request": {  
                "id": null,  
                "idempotency_key": null  
            },  
            "type": "customer.subscription.updated"  
        },  
        {  
            "id": "evt_2",  
            "object": "event",  
            "api_version": "2018-02-28",  
            "created": 1642532021,  
            "data": {  
                "object": {  
                    "id": "sub_2",  
                    "object": "subscription",  
                    "application_fee_percent": null  
                },  
                "previous_attributes": {  
                    "current_period_end": 1642331884,  
                    "current_period_start": 1652445484  
                }  
            },  
            "livemode": false,  
            "pending_webhooks": 0,  
            "request": {  
                "id": null,  
                "idempotency_key": null  
            },  
            "type": "customer.subscription.updated"  
        }  
    ],  
    "has_more": true,  
    "url": "/v1/events"  
}

svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-01-19T18:52:23.09+00:00

Hello @ralphjjr ,

Thanks for the question and using MS Q&A platform.

I think there is a difference in the pagination feature with regards to Copy Activity and Data flow. I am currently checking at my end.
svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-01-20T15:39:41.307+00:00

Hello @ralphjjr ,

Can you try the below and let me know if this works.

{data[99].id} in place of data[99].id .

Just the braces {} in addition to the existing ones.
svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-01-25T14:46:51.557+00:00

Hello @ralphjjr ,

Just checking in if you have had a chance to see the previous response.
svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-02-01T10:42:58.11+00:00

Hello @ralphjjr ,

Just checking in if you have had a chance to see the previous response.
ralphjjr 101 Reputation points

2022-02-01T22:08:31.543+00:00

Yes, that does not work. I still only get the first page.
ralphjjr 101 Reputation points

2022-02-01T23:44:48.88+00:00
So that didn't work - I've tried a few other things, I even tried a different API where the value I need is not inside an array.
The response from the other api looks like this:

{ "next_offset": 100, "records": [ { "id": 1 }, { "id": 2 }, { "id": 3 } ] }

In this example if the next_offset is not -1 I can call the api again with a query parameter offset, equal to the next_offset.
In a pipeline copy activity it works flawlessly.

In a dataflow, the same pagination rules do not work. I thought maybe it was trying to paginate but just hitting pg 1. I added a MaxRequestNumber = 4 and I was right. It is paginating, but incorrectly.

I've also tried setting it to:

{next_offset}

$.next_offset

{$.next_offset}

I even tried different things for the query:

offset

{offset}

It just seems that it's not ever adding the query parameter offset to the subsequent pagination calls.

Here is the data preview output I see:

So it has the first request, and then 4 more, I imagine because of the MaxRequestNumber - but you can see the next_offset is the same, so it's getting the same page over and over.
Joseph Fitzgerald 0 Reputation points

2023-02-01T06:48:22.2066667+00:00

I have pretty much the same problem. I have pagination working in the Copy activity, but it doens't work in Data Flow.

The API returns JSON and includes a Bookmark and MoreRowsExist values. The API can take a bookmark parameter.

Copy activity (works):

It wasn't easy to get the Copy activity to work... I basically had to reverse engineer it by trying combinations of $ . and {}. Now I'm faced with the same challenge in the Data Flow.

@ralphjjr did you ever figure it out?

svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-01-19T18:52:23.09+00:00

Hello @ralphjjr ,

Thanks for the question and using MS Q&A platform.

I think there is a difference in the pagination feature with regards to Copy Activity and Data flow. I am currently checking at my end.
svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-01-20T15:39:41.307+00:00

Hello @ralphjjr ,

Can you try the below and let me know if this works.

{data[99].id} in place of data[99].id .

Just the braces {} in addition to the existing ones.
svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-01-25T14:46:51.557+00:00

Hello @ralphjjr ,

Just checking in if you have had a chance to see the previous response.
svijay-MSFT 5,121 Reputation points Microsoft Employee

2022-02-01T10:42:58.11+00:00

Hello @ralphjjr ,

Just checking in if you have had a chance to see the previous response.
ralphjjr 101 Reputation points

2022-02-01T22:08:31.543+00:00

Yes, that does not work. I still only get the first page.
ralphjjr 101 Reputation points

2022-02-01T23:44:48.88+00:00

So that didn't work - I've tried a few other things, I even tried a different API where the value I need is not inside an array.
The response from the other api looks like this:

{ "next_offset": 100, "records": [ { "id": 1 }, { "id": 2 }, { "id": 3 } ] }

In this example if the next_offset is not -1 I can call the api again with a query parameter offset, equal to the next_offset.
In a pipeline copy activity it works flawlessly.

In a dataflow, the same pagination rules do not work. I thought maybe it was trying to paginate but just hitting pg 1. I added a MaxRequestNumber = 4 and I was right. It is paginating, but incorrectly.

I've also tried setting it to:

{next_offset}

$.next_offset

{$.next_offset}

I even tried different things for the query:

offset

{offset}

It just seems that it's not ever adding the query parameter offset to the subsequent pagination calls.

Here is the data preview output I see:

So it has the first request, and then 4 more, I imagine because of the MaxRequestNumber - but you can see the next_offset is the same, so it's getting the same page over and over.
Joseph Fitzgerald 0 Reputation points

2023-02-01T06:48:22.2066667+00:00

I have pretty much the same problem. I have pagination working in the Copy activity, but it doens't work in Data Flow.

The API returns JSON and includes a Bookmark and MoreRowsExist values. The API can take a bookmark parameter.

Copy activity (works):

It wasn't easy to get the Copy activity to work... I basically had to reverse engineer it by trying combinations of $ . and {}. Now I'm faced with the same challenge in the Data Flow.

@ralphjjr did you ever figure it out?

REST Pagination in a Data Flow

Activity