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

Raj D 491 Reputation points
2022-12-30T04:20:12.507+00:00

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.

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

Accepted answer
  1. Nandan Hegde 21,606 Reputation points
    2022-12-30T04:49:29.2+00:00

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

    275013-image.png

    274940-image.png

    275004-image.png

    274916-image.png

    you can replace the set variable activity with Copy activity


0 additional answers

Sort by: Most helpful