it shows 2week_ago cannot start with a numbert and I cannot do it well. Please let me know how to solve this problem.

Question

I would like to get data for every two weeks from daily dates.

I have created it in one step, but it shows 2week_ago cannot start with a numbert and I cannot do it well. Please let me know how to solve this problem.

(ex.) 2023/03/14~2023/03/28
2023/03/15~2023/03/28

The data structure is published/src_sap/delta/sales_detail/year=yyyy/month=mm/day=dd.

A file is stored for each of these dates, and we would like to retrieve a parcel file for each of these dates for two weeks every day.

The following is JSON.

{
    "name": "Pipeline1",
    "properties": {
        "activities": [
            {
                "name": "Until1",
                "type": "Until",
                "dependsOn": [
                    {
                        "activity": "set_variable_2week_ago",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    },
                    {
                        "activity": "set_variable_yesterday",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "expression": {
                        "value": "@equals(variables('2week_ago'),variables('today'))",
                        "type": "Expression"
                    },
                    "activities": [
                        {
                            "name": "Copy data1",
                            "type": "Copy",
                            "dependsOn": [
                                {
                                    "activity": "Set variable1",
                                    "dependencyConditions": [
                                        "Succeeded"
                                    ]
                                }
                            ],
                            "policy": {
                                "timeout": "0.12:00:00",
                                "retry": 0,
                                "retryIntervalInSeconds": 30,
                                "secureOutput": false,
                                "secureInput": false
                            },
                            "userProperties": [],
                            "typeProperties": {
                                "source": {
                                    "type": "ParquetSource",
                                    "storeSettings": {
                                        "type": "AzureBlobFSReadSettings",
                                        "recursive": true,
                                        "wildcardFolderPath": {
                                            "value": "@src_sap/delta/sales_detail/,variables('temp')",
                                            "type": "Expression"
                                        },
                                        "wildcardFileName": "sap_sales_detail_YEAR_IND_CY_CY_1*.parquet",
                                        "enablePartitionDiscovery": false
                                    }
                                },
                                "sink": {
                                    "type": "SqlPoolSink",
                                    "writeBehavior": "Insert",
                                    "sqlWriterUseTableLock": false
                                },
                                "enableStaging": false,
                                "translator": {
                                    "type": "TabularTranslator",
                                    "typeConversion": true,
                                    "typeConversionSettings": {
                                        "allowDataTruncation": true,
                                        "treatBooleanAsNumber": false
                                    }
                                }
                            },
                            "inputs": [
                                {
                                    "referenceName": "Parquet1",
                                    "type": "DatasetReference"
                                }
                            ],
                            "outputs": [
                                {
                                    "referenceName": "ds_synw__syndp",
                                    "type": "DatasetReference",
                                    "parameters": {
                                        "prm_schema": "schema_name",
                                        "prm_table": "table_name"
                                    }
                                }
                            ]
                        },
                        {
                            "name": "Set variable1",
                            "type": "SetVariable",
                            "dependsOn": [],
                            "userProperties": [],
                            "typeProperties": {
                                "variableName": "temp",
                                "value": {
                                    "value": "@concat('year=',addDays(substring(variables('2week_ago'),0,3),1),'month=',addDays(substring(variables('2week_ago'),5,6),1),'day=',addDays(substring(variables('2week_ago'),8,9),1))",
                                    "type": "Expression"
                                }
                            }
                        }
                    ],
                    "timeout": "0.12:00:00"
                }
            },
            {
                "name": "set_variable_2week_ago",
                "type": "SetVariable",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "2week_ago",
                    "value": {
                        "value": "@concat(formatDateTime(addDays(utcNow(),-15),'yyyy'),'/',formatDateTime(addDays(utcNow(),-15),'MM'),'/',formatDateTime(addDays(utcNow(),-15),'dd'))",
                        "type": "Expression"
                    }
                }
            },
            {
                "name": "set_variable_yesterday",
                "type": "SetVariable",
                "dependsOn": [],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "today",
                    "value": {
                        "value": "@concat(formatDateTime(addDays(utcNow(),-1),'yyyy'),'/',formatDateTime(addDays(utcNow(),-1),'MM'),'/',formatDateTime(addDays(utcNow(),-1),'dd'))",
                        "type": "Expression"
                    }
                }
            }
        ],
        "variables": {
            "today": {
                "type": "String"
            },
            "2week_ago": {
                "type": "String"
            },
            "temp": {
                "type": "String"
            }
        },
        "folder": {
            "name": "2weekcopydata"
        },
        "annotations": []
    }
}

Answer 1

Hi @NishimuraChinatsu-9854 ,

Thanks for using Microsoft Q&A forum and posting your query.

The error message 2week_ago cannot start with a number is because the variable name 2week_ago starts with a number, which is not allowed in Azure Data Factory. To overcome this error, you can rename the variable to start with a letter or an underscore. For example, you can rename 2week_ago to two_week_ago or week_ago_2.

For Example:

This will help overcome the error you are receiving.

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.