Azure data factory ForEach activity to append year and month to the file name

Question

Greetings!!!

I'm using the below query to iterate over using ForEach activity. I would like to save my .csv file with the year and month appended and saved to the container.

SQL Query:

SELECT 202210 AS [YearMM]  
 UNION ALL  
SELECT 202211 AS [YearMM]  
 UNION ALL  
SELECT 202212 AS [YearMM]  

For instance when I read the value 202210 I would want to save the file as Sales_202210.csv, And the next 2 files should be Sales_202211.csv, Sales_202212.csv. I tried using @concat('Sales_',item().YearMM) or activity.output which gives the output as below.

Sales_{"YearMM":202210}[{"YearMM":202210},{"YearMM":202211},{"YearMM":202212}].csv   
Sales_{"YearMM":202211}[{"YearMM":202210},{"YearMM":202211},{"YearMM":202212}].csv   
Sales_{"YearMM":202212}[{"YearMM":202210},{"YearMM":202211},{"YearMM":202212}].csv   

Could you please point to me if I'm missing something.

Thank you in advance.

Answer 1

Hey,

Sample JSON:
{
"name": "pipeline8",
"properties": {
"activities": [
{
"name": "Lookup1",
"type": "Lookup",
"dependsOn": [],
"policy": {
"timeout": "0.12:00:00",
"retry": 0,
"retryIntervalInSeconds": 30,
"secureOutput": false,
"secureInput": false
},
"userProperties": [],
"typeProperties": {
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": " SELECT 202210 AS [YearMM]\n UNION ALL\n SELECT 202211 AS [YearMM]\n UNION ALL\n SELECT 202212 AS [YearMM]",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"dataset": {
"referenceName": "DS",
"type": "DatasetReference"
},
"firstRowOnly": false
}
},
{
"name": "ForEach1",
"type": "ForEach",
"dependsOn": [
{
"activity": "Lookup1",
"dependencyConditions": [
"Succeeded"
]
}
],
"userProperties": [],
"typeProperties": {
"items": {
"value": "@activity('Lookup1').output.value",
"type": "Expression"
},
"isSequential": true,
"activities": [
{
"name": "Set variable1",
"type": "SetVariable",
"dependsOn": [],
"userProperties": [],
"typeProperties": {
"variableName": "Test",
"value": {
"value": "@markus.bohland@hotmail.de ('Sales_',item().yearmm)",
"type": "Expression"
}
}
}
]
}
}
],
"variables": {
"Test": {
"type": "String"
}
},
"annotations": []
}
}

you can replace the set variable activity with Copy activity