End condition for pagination rules for copy activty in Azure Data Factory

Question

Dear Azure Community. I would ask you about pagination rule for rest api in copy activities In Azure Data Factory.

Task description:

We have pipeline for loading data from application using rest API, and in this rest api end condition is when bookmark link in array Links in 0 row is the same with link in 1 row (Screen 1 below).Copy Activities we should use in Foreach activities for lot of runs.

Screen 1 Links objects in Json result of API

Attempts to solve the task:

Main problem in Copy activities, that we have in Foreach this copy activities and rule for end condition when links[1].href  = links[0].href (screen 2 ) doesn’t work and loop is not over. 

Screen 2 End condition for links[1].href  = links[0].href

When we tried upload value from Lookup activity by formula (Screen 3) it doesn’t work also.

Screen 3 Test for formula for end condition using value from Lookup activity

End condition work only when we add for Const position manual link for this rule (Screen 4). But unfortunately, in foreach activity this is not practical.

Screen 4 The only working end condition for Pagination rule when link entered manually

Question:

How can we solve this problem with end condition in Azure Data Factory?

Answer 1

@Gulermak Reports

Thanks for using MS Q&A platform and posting your query.

The problem you're facing with the end condition in Azure Data Factory's copy activity pagination rule is a common challenge when dealing with dynamic values within a loop. Here are two possible solutions to achieve the desired outcome:

 Using a Until Loop

1. Set a Variable: Create a variable in ADF to store the link value from the previous iteration (e.g., previousLink).
2. Until Loop: Implement a Until loop within the foreach activity. The loop condition should be @equals(variables('previousLink'), @activity('CopyActivity1').output.firstRow.Links[0].href). This checks if the previousLink variable is equal to the first element's href property in the current iteration's Links array.
3. Update Variable: Inside the loop, after the copy activity (CopyActivity1 in this example) finishes, update the previousLink variable with the current iteration's first element's href property using a similar expression: set_variable('previousLink', @activity('CopyActivity1').output.firstRow.Links[0].href

Custom Script

1. Add a Web Activity: Add a web activity after the copy activity within the foreach loop.
2. Script Logic: Within the web activity, use a scripting language like PowerShell or Python to access the previous iteration's output and compare the href values as needed. You can leverage ADF functions like activity('CopyActivity1').output to access the previous copy activity's output.
3. Exit Code: Based on the comparison result, set the web activity's exit code to indicate the loop continuation (non-zero) or termination (zero).

Hope this helps. Do let us know if you any further queries.