construct foreach loop for array of dates

arkiboys 9,666 Reputation points
2022-10-13T11:57:51.703+00:00

Hello,
I have a datafactory pipeline with alot of activities.
one of the activities in th epipeline is a copy activity where the source is using something like the following expression:

@markus.bohland@hotmail.de (
dataset().dateFieldName, ' gt 2018-05-30 ',
' and ', dataset().dateFieldName, ' lt 2019-01-01',
' ', dataset().filters
)

as you see above, I have to manually change the dates accordingly...

I would like to send only the following dates to the copy activity so that for each loop the appropriate dates are used.
for example:

loop1 --> 2018-05-30, 2019-01-01 --> shown in the example above
loop2 --> 2018-12-31, 2020-01-01
loop3 --> 2019-12-31, 2021-01-01
loop4 --> 2020-12-31, 2022-01-01
loop5 --> 2021-12-31, 2022-09-02

I do not know how to construct the loop correctly.

thank you

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,751 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nandan Hegde 29,906 Reputation points MVP
    2022-10-13T13:30:28.977+00:00

    Hey,
    Create a parameter of type String and concatenate your values with a '|' separator
    eg: 2018-05-30,2019-01-01|2018-12-31,2020-01-01

    Then use Split function on that parameter based on '|' for the number of iterations.

    Now within the foreach use split again on item() but on ',' and leverage element [0] and element [1]

    please refer the below JSON

    {  
        "name": "pipeline7",  
        "properties": {  
            "activities": [  
                {  
                    "name": "ForEach1",  
                    "type": "ForEach",  
                    "dependsOn": [],  
                    "userProperties": [],  
                    "typeProperties": {  
                        "items": {  
                            "value": "@split(pipeline().parameters.Test,'|' )",  
                            "type": "Expression"  
                        },  
                        "isSequential": true,  
                        "activities": [  
                            {  
                                "name": "Wait1",  
                                "type": "Wait",  
                                "dependsOn": [],  
                                "userProperties": [],  
                                "typeProperties": {  
                                    "waitTimeInSeconds": 1  
                                }  
                            },  
                            {  
                                "name": "Set variable1",  
                                "type": "SetVariable",  
                                "dependsOn": [],  
                                "userProperties": [],  
                                "typeProperties": {  
                                    "variableName": "Dummy1",  
                                    "value": {  
                                        "value": "@split(item(),',')[0]",  
                                        "type": "Expression"  
                                    }  
                                }  
                            },  
                            {  
                                "name": "Set variable2",  
                                "type": "SetVariable",  
                                "dependsOn": [],  
                                "userProperties": [],  
                                "typeProperties": {  
                                    "variableName": "Dummy2",  
                                    "value": {  
                                        "value": "@split(item(),',')[1]",  
                                        "type": "Expression"  
                                    }  
                                }  
                            }  
                        ]  
                    }  
                }  
            ],  
            "parameters": {  
                "Test": {  
                    "type": "string",  
                    "defaultValue": "2018-05-30,2019-01-01|2018-12-31,2020-01-01"  
                }  
            },  
            "variables": {  
                "Dummy1": {  
                    "type": "String"  
                },  
                "Dummy2": {  
                    "type": "String"  
                }  
            },  
            "annotations": []  
        }  
    }  
    

0 additional answers

Sort by: Most helpful