@JBorn I’m currently working on a similar implementation and here’s how I tackled that problem:
Define initial variables:
* “After” - 0
* “PagingFound” - true
* “TempPageCount” - 1
* “CurrentPageCount” - 1
* “TotalPageCount” - 0
Until Activity
* Settings → Expression > @equals(variables(‘PagingFound’), ‘false’)
Inside Until Activity
* Copy Data Activity > HubSpot Search API to DataLake
Request method: POST
Request body:
@{concat('{
"limit": ', variables('Limit'), ',
"after": ',variables('After'),',
"filterGroups": [
{
"filters": [
{
"operator": "GTE",
"propertyName": "lastmodifieddate",
"value": "',variables('LastModifiedDate'),'"
}
]
}
],
"properties":[',variables('HubSpotContactProperties'),'],
"sorts": [
"string"
]
}')}
Sink: JSON file with FileName appending the CurrentPageCount to create a new file for each batch from HubSpot
On Success for the Copy Data Activity > Lookup Activity to get the First Row only of that file - need to determine if it contains the “paging” object in the JSON.
On Failure for the Copy Data Activity > Set Variable - “PagingFound” = false
On Success for the Lookup Activity > If Condition Activity
* @if(contains(activity(‘Result count’).output.firstRow, ‘paging’), true, false)
On Failure for the Lookup Activity > Set Variable - “PagingFound” = false
If Condition Activity > True
* Set PagingFound = true
* Set After = @{activity(‘Result count’).output.firstrow.paging.next.after}
* Set TempPageCount = @string(add(int(variables(‘CurrentPageCount’)), 1))
* Set CurrentPageCount = TempPageCount
If Condition Activity > False > Set Variable - PagingFound = false
Main Pipeline
Until Activity
After the Until Activity completes, I reset the page counts,. then iterate over the DataLake files and insert those in my Azure SQL tables. It’s a bit of a hack and additional work around, but it was the only way to pass the “After” to the Request Body for the Search API call.
Let me know if you have any questions. So far this implementation has worked well for our needs (small delta sets - large datasets of 100K+ records would be pretty brutal using the API, regardless of the implementation).