How to pass a pipeline variable into a Dataset Parameter?

Question

I'm currently working on a pipeline which requires me to dynamically create a @FileName(FileName_YYYY_MM_DD.csv) variable which has the date appended to it.

The aim is to export data from an Azure SQL Database and create a CSV file with a dynamic @FileName in my data lake.

enter image description here

1. I have a lookup activity which triggers a SQL script which produces 1 line (FileName_YYYY_MM_DD.csv)
2. That value is returned as an Array so I store it in a variable @FileNames
3. I then have another Set Variable Activity which extracts the file name as a string and stores it into another variable called @FileName
4. I'm then trying to pass the pipeline variable @FileName to a Dataset Parameter in a CopyData Activity Sink which links to my Data Lake.

However, when I try to run my pipeline I get an error which is not very descriptive:

{
"code": "BadRequest",
"message": null,
"target": "pipeline//runid/*******",
"details": null,
"error": null
}

I've looked at some similar issues but everyone suggests that I have a white space somewhere. I've double-checked my strings and I don't have any white spaces.

Could you please help me pass the variable to my dataset parameter?

However, when I try to run my pipeline I get an error which is not very descriptive:

{
"code": "BadRequest",
"message": null,
"target": "pipeline//runid/*******",
"details": null,
"error": null
}

I've looked at some similar issues but everyone suggests that I have a white space somewhere. I've double-checked my strings and I don't have any white spaces.

Answer 1

{ "code": "BadRequest", "message": null, "target": "pipeline//runid/*******", "details": null, "error": null }

In ADF, this error occurs only when we gave wrong dynamic content or any special characters like (!@#..) in dynamic content.

You can see I have same error when I gave the below dynamic content.

@activity('Lookup1').output.value[0].name#

I have reproduced your scenario and able to get the requirement done and this is my pipeline JSON.

{
    "name": "pipeline2",
    "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",
                        "queryTimeout": "02:00:00",
                        "partitionOption": "None"
                    },
                    "dataset": {
                        "referenceName": "AzureSqlTable1",
                        "type": "DatasetReference"
                    },
                    "firstRowOnly": false
                }
            },
            {
                "name": "Set variable1",
                "type": "SetVariable",
                "dependsOn": [
                    {
                        "activity": "Lookup1",
                        "dependencyConditions": [
                            "Succeeded"
                        ]
                    }
                ],
                "userProperties": [],
                "typeProperties": {
                    "variableName": "filename",
                    "value": {
                        "value": "@activity('Lookup1').output.value[0].name",
                        "type": "Expression"
                    }
                }
            },
            {
                "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": "AzureSqlSource",
                        "queryTimeout": "02:00:00",
                        "partitionOption": "None"
                    },
                    "sink": {
                        "type": "DelimitedTextSink",
                        "storeSettings": {
                            "type": "AzureBlobFSWriteSettings"
                        },
                        "formatSettings": {
                            "type": "DelimitedTextWriteSettings",
                            "quoteAllText": true,
                            "fileExtension": ".txt"
                        }
                    },
                    "enableStaging": false,
                    "translator": {
                        "type": "TabularTranslator",
                        "typeConversion": true,
                        "typeConversionSettings": {
                            "allowDataTruncation": true,
                            "treatBooleanAsNumber": false
                        }
                    }
                },
                "inputs": [
                    {
                        "referenceName": "AzureSqlTable1",
                        "type": "DatasetReference"
                    }
                ],
                "outputs": [
                    {
                        "referenceName": "sinkfile",
                        "type": "DatasetReference",
                        "parameters": {
                            "FileName": {
                                "value": "@variables('filename')",
                                "type": "Expression"
                            }
                        }
                    }
                ]
            }
        ],
        "variables": {
            "filename": {
                "type": "String"
            }
        },
        "annotations": []
    }
}