英文:

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

问题

我需要安排一个在每个月的第10个工作日运行的Azure数据工厂管道。星期六和星期天不算在工作日内。例如，如果我们排除星期六和星期天，那么2023年6月的第10个工作日是6月14日。下一个第10个工作日是28日，依此类推。因此，ADF管道应在这些日期运行。

尝试了滚动窗口触发器，但不确定如何实现。

英文:

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.

答案1

得分: 1

你不能通过常规的ADF触发器来实现这个目标。

你可以在几个月的特定日期触发管道，例如10、11、12、13、14号，并运行一些代码来检查今天是否是工作日。你还需要在接下来的几天内检查你的流程是否已经运行过，可以使用某个配置表或文件来实现。

英文:

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.

答案2

得分: 0

以下是翻译好的内容：

• 您可以执行以下步骤来满足要求。触发器可能无法帮助您实现要求。我已使用管道活动来确定一个月中是10的倍数的工作日。以下是我所使用的步骤。

• 首先获取特定月份的开始日期。使用一个 until 循环，它会迭代直到月底。

• 在 until 活动中，我使用了一系列变量活动来获取该月份的所有日期。

• 现在使用一个筛选活动来筛选掉星期日或星期六的日期。

• 最后，选择是 n 的倍数的工作日（其中 n=10，例如）。

• 以下是上述管道的完整 JSON：

{
    "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"
            },
           

<details>
<summary>英文:</summary>

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](https://i.imgur.com/zadDsRF.png)
- For 20th business day in June
![enter image description here](https://i.imgur.com/VXP5fEu.png)
- 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.
</details>