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.
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.
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": []
}
}
