CODEWITHSUNDEEP
azureazure-data-factoryforeach-loop-container
Serdia
Serdia

Reputation: 4428

Why SetVariable inside Foreach loop is not iterating as expected in Azure Data Factory

I have my DataFolder on local that contains subfolders. Each subfolder has a date in its name and can have multiple excel files inside.

enter image description here

ForEach activity iterating through each subfolder name and loading data from each excel into sql table.

Set variables1 substrings only a data part from subfolder name Set variable2 simply replacing dots (in a date) with "/"

The question is, why the result sql table only contains 5-31-2023 date? What happened with 06-30-2023?

enter image description here

SetVariable2 has expression for variable DatePart.

@substring( item().name , add(lastIndexOf(item().name, ' '), 1) , sub(length(item().name), add(lastIndexOf(item().name, ' '),1)))

Next, I am using variable DatePart in a Copy Data activity as a new column.

enter image description here

Result from sql table:

![enter image description here

UPDATE:

Rebuild the pipeline using @RakeshGovindula way. So why in his way the sequence of executions occurs the wat it should be

enter image description here

However, in my case the sequence of executions is incorrect order. Everything is the same, even expressions.

enter image description here

Upvotes: 0

Views: 540

Answers (1)

Rakesh Govindula
Rakesh Govindula

Reputation: 11549

I tried your scenario and it's working fine for me.

These are my folders and for simple demo, I took same filename mycsv2.csv in these two folders.

enter image description here

I will insert data from these 2 CSVs to a single SQL table with the additional columns as follows.

After giving Get meta data child items to ForEach, inside ForEach, use the below expression to get date part (with /) from the @item().name.

@replace(split(item().name,' ')[1],'.','/')

enter image description here

And give additional columns same.

enter image description here

the Datepart will give the result like this in each iteration.

enter image description here

Result data in SQL table:

enter image description here

Also, as you inserting data from each file into SQL table in each iteration, make sure you enable Sequential check in the ForEach.

This is my pipeline JSON for your reference:

{
    "name": "pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Get Metadata1",
                "type": "GetMetadata",
                "dependsOn": [],
                "policy": {
                    "timeout": "0.12:00:00",
                    "retry": 0,
                    "retryIntervalInSeconds": 30,
                    "secureOutput": false,
                    "secureInput": false
                },
                "userProperties": [],
                "typeProperties": {
                    "dataset": {
                        "referenceName": "sourcefiles",
                        "type": "DatasetReference"
                    },
                    "fieldList": [
                        "childItems"
                    ],
                    "storeSettings": {
                        "type": "AzureBlobFSReadSettings",
                        "enablePartitionDiscovery": false
                    },
                    "formatSettings": {
                        "type": "DelimitedTextReadSettings"
                    }
                }
            },
            {
                "name": "ForEach1",
                "type": "ForEach",
                "dependsOn": [
                    {
                        "activity": "Get Metadata1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "items": {
                        "value": "@activity('Get Metadata1').output.childItems",
                        "type": "Expression"
                    },
                    "isSequential": true,
                    "activities": [
                        {
                            "name": "Get Metadata2",
                            "type": "GetMetadata",
                            "dependsOn": [],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "dataset": {
                                    "referenceName": "subfolders_getmetadata",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "foldername": {
                                            "value": "@item().name",
                                            "type": "Expression"
                                        }
                                    }
                                },
                                "fieldList": [
                                    "childItems"
                                ],
                                "storeSettings": {
                                    "type": "AzureBlobFSReadSettings",
                                    "enablePartitionDiscovery": false
                                },
                                "formatSettings": {
                                    "type": "DelimitedTextReadSettings"
                                }
                            }
                        },
                        {
                            "name": "split and replace with slash",
                            "type": "SetVariable",
                            "dependsOn": [
                                {
                                    "activity": "Get Metadata2",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "Datepart",
                                "value": {
                                    "value": "@replace(split(item().name,' ')[1],'.','/')",
                                    "type": "Expression"
                                }
                            }
                        },
                        {
                            "name": "Copy data1",
                            "type": "Copy",
                            "dependsOn": [
                                {
                                    "activity": "split and replace with slash",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "source": {
                                    "type": "DelimitedTextSource",
                                    "additionalColumns": [
                                        {
                                            "name": "ItemName",
                                            "value": {
                                                "value": "@item().name",
                                                "type": "Expression"
                                            }
                                        },
                                        {
                                            "name": "FileDatevariable",
                                            "value": {
                                                "value": "@variables('Datepart')",
                                                "type": "Expression"
                                            }
                                        }
                                    ],
                                    "storeSettings": {
                                        "type": "AzureBlobFSReadSettings",
                                        "recursive": true,
                                        "enablePartitionDiscovery": false
                                    },
                                    "formatSettings": {
                                        "type": "DelimitedTextReadSettings"
                                    }
                                },
                                "sink": {
                                    "type": "AzureSqlSink",
                                    "writeBehavior": "insert",
                                    "sqlWriterUseTableLock": false,
                                    "tableOption": "autoCreate",
                                    "disableMetricsCollection": false
                                },
                                "enableStaging": false,
                                "translator": {
                                    "type": "TabularTranslator",
                                    "typeConversion": true,
                                    "typeConversionSettings": {
                                        "allowDataTruncation": true,
                                        "treatBooleanAsNumber": false
                                    }
                                }
                            },
                            "inputs": [
                                {
                                    "referenceName": "csvfile",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "foldername": {
                                            "value": "@item().name",
                                            "type": "Expression"
                                        }
                                    }
                                }
                            ],
                            "outputs": [
                                {
                                    "referenceName": "AzureSqlTable1",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "tablename": {
                                            "value": "@split(item().name,' ')[0]",
                                            "type": "Expression"
                                        }
                                    }
                                }
                            ]
                        }
                    ]
                }
            }
        ],
        "variables": {
            "Datepart": {
                "type": "String"
            }
        },
        "annotations": []
    }
}

Upvotes: 1

Related Questions