Question 189520 in Azure Question posted in
The official documentation to get you started can be found here.

How to schedule an azure datafactory pipeline to run on every 'n'th business day of a month

I need to schedule an Azure data factory pipeline that runs every 10th business day of the month. Saturday & Sunday are not business days. for example, if we exclude Sat & Sun, then 10th Business day for Jun 2023 falls on 14th June. The next 10th business day falls on 28th and so on. So the ADF pipeline should run on those dates.

Tried tumbling window trigger, but not sure how to achieve it.

Tags:

2

Answers


  1. 0

    You cannot achieve this with regular ADF triggers.

    You can trigger the pipeline on a few month days, i.e. 10,11,12,13,14 , and run some code to check if today is a business day. You will also need to check on the following days that your process hasn’t already run, by using some config table or file.

    Login or Signup to reply.
  2. 0

    You can do the following steps to achieve the requirement. Triggers might not help you to achieve your requirement. I have used pipeline activities to determine the business days which are multiples of 10 in a month. The following are the steps that I have used.

    • First get the start date of a particular month. Use an until loop which iterates until the last day of the month.

    • In until activity, I have used a combination of variable activities to get all the dates in that month.

    • Now use a filter activity to filter out the days where its Sunday or Saturday.

    • Finally, select the business days that are multiple of n (where n=10 for example).

    • The following is the complete pipeline JSON for the above:

    {
    "name": "pipeline2",
    "properties": {
        "activities": [
            {
                "name": "Until1",
                "type": "Until",
                "dependsOn": [
                    {
                        "activity": "month_start",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "expression": {
                        "value": "@equals(variables('flag'),startOfMonth(addDays(variables('month_start'),35)))",
                        "type": "Expression"
                    },
                    "activities": [
                        {
                            "name": "append date",
                            "type": "AppendVariable",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "dates",
                                "value": {
                                    "value": "@variables('flag')",
                                    "type": "Expression"
                                }
                            }
                        },
                        {
                            "name": "update flag using temp",
                            "type": "SetVariable",
                            "dependsOn": [
                                {
                                    "activity": "append date",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "temp",
                                "value": {
                                    "value": "@addDays(variables('flag'),1)",
                                    "type": "Expression"
                                }
                            }
                        },
                        {
                            "name": "update flag",
                            "type": "SetVariable",
                            "dependsOn": [
                                {
                                    "activity": "update flag using temp",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "flag",
                                "value": {
                                    "value": "@variables('temp')",
                                    "type": "Expression"
                                }
                            }
                        }
                    ],
                    "timeout": "0.12:00:00"
                }
            },
            {
                "name": "initialize flag",
                "type": "SetVariable",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "flag",
                    "value": {
                        "value": "@formatDateTime(startOfMonth(utcNow()))",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "month_start",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "initialize flag",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "variableName": "month_start",
                    "value": {
                        "value": "@variables('flag')",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "Filter1",
                "type": "Filter",
                "dependsOn": [
                    {
                        "activity": "Until1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@variables('dates')",
                        "type": "Expression"
                    },
                    "condition": {
                        "value": "@not(or(equals(dayOfWeek(item()),0),equals(dayOfWeek(item()),6)))",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Filter1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@range(0,length(activity('Filter1').output.value))",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "If Condition1",
                            "type": "IfCondition",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "expression": {
                                    "value": "@equals(mod(add(item(),1),pipeline().parameters.nth_day),0)",
                                    "type": "Expression"
                                },
                                "ifTrueActivities": [
                                    {
                                        "name": "Set variable1",
                                        "type": "SetVariable",
                                        "dependsOn": [],
                                        "policy": {
                                            "timeout": "0.12:00:00",
                                            "retry": 0,
                                            "retryIntervalInSeconds": 30,
                                            "secureOutput": false,
                                            "secureInput": false
                                        },
                                        "userProperties": [],
                                        "typeProperties": {
                                            "variableName": "tp",
                                            "value": {
                                                "value": "@activity('Filter1').output.value[item()]",
                                                "type": "Expression"
                                            }
                                        }
                                    }
                                ]
                            }
                        }
                    ]
                }
            }
        ],
        "parameters": {
            "nth_day": {
                "type": "int",
                "defaultValue": 10
            }
        },
        "variables": {
            "flag": {
                "type": "String"
            },
            "temp": {
                "type": "String"
            },
            "dates": {
                "type": "Array"
            },
            "month_start": {
                "type": "String"
            },
            "final_dates_to_trigger": {
                "type": "Array"
            },
            "tp": {
                "type": "String"
            }
        },
        "annotations": []
    }
}
    • For 10th business day in June

    enter image description here

    • For 20th business day in June

    enter image description here

    • You can replace the set variable activity inside if condition with execute pipeline activity and schedule to run this pipeline daily. You will have to change the condition to also check if today’s date is equal to any of the nth (n=10) element of the filter activity output date as well.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search