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.

Answer 2

Hello @Gulermak Reports

You mentioned that the end condition is when the bookmark link in the array Links in the 0 row is the same as the link in the 1 row.

However, you are having trouble getting this end condition to work in Copy Activities within a Foreach activity. One thing you could try is using the "Get Metadata" activity to retrieve the bookmark link from the REST API response and store it in a variable.

Then, you can use this variable in your end condition expression. Here's an example of what the expression might look like:

@equals(activity('Get Metadata').output.firstRow.Links[0].href, variables('bookmarkLink'))

In this example, "Get Metadata" is the name of the activity that retrieves the bookmark link, and "bookmarkLink" is the name of the variable that stores the bookmark link.

You can also try using the "Until" activity to loop until the end condition is met. Here's an example of what the "Until" activity might look like:

{
    "name": "Until",
    "type": "Until",
    "dependsOn": [
        {
            "activity": "Get Metadata",
            "dependencyConditions": [
                "Succeeded"
            ]
        }
    ],
    "userProperties": [],
    "typeProperties": {
        "expression": {
            "value": "@equals(activity('Get Metadata').output.firstRow.Links[0].href, variables('bookmarkLink'))",
            "type": "Expression"
        },
        "activities": [
            {
                "name": "Copy Data",
                "type": "Copy",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "source": {
                        "type": "RestSource"
                    },
                    "sink": {
                        "type": "BlobSink"
                    }
                }
            }
        ]
    }
}

In this example, the "Until" activity loops until the end condition is met, and then it executes the "Copy Data" activity. You can replace "Copy Data" with your own activity that copies data from the REST API.

That should get you started

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.