Mixed properties in 'source' column/fields in Azure Data Factory

Question

I'm using Azure Data Factory to copy data from a source folder (Azure Blob) that has multiple folders inside it (and each one of those folders has a year as it's name, and inside the folders are the Excel spreadsheets with the data) to a SQL Server table. I want to iterate through the folders, select the folder name, and insert the name into a column in a table, so for each data read inside the files in the folder, the folder name where this data is will be in the table, like this:

Data 1   |Data 2   |Year
------------------------
A        |abc      |2020
B        |def      |2020
C        |ghi      |2021
D        |jkl      |2022
E        |lmn      |2023

My pipeline is like this:

I have a Get Metadata activity called Get Metadata1 pointing to the folders, and after that a ForEach to iterate through the folders with two activities: one "Set variable" activity setting a variable named FolderYear with @item().name as value (to select the folder name) and a Copy activity which creates a additional column into the dataset named Year using the variable.

I'm trying to map the additional Year column to a column in the table, but when I debug the pipeline, the following error appears:

{ "errorCode": "2200", "message": "Mixed properties are used to reference 'source' columns/fields in copy activity mapping. Please only choose one of the three properties 'name', 'path' and 'ordinal'. The problematic mapping setting is 'name': 'Year', 'path': '','ordinal': ''. ", "failureType": "UserError", "target": "Copy data1", "details": [] }

It's possible to insert the folder name which I'm currently iterating into a database column?

Joseph Xu Joseph Xu · Accepted Answer · 2020-09-14T01:50:41

I've made a same test and copied the data(include the folder name) into a SQL table successfully.
I have two folders in the container and each folder contains one cvs file for test.

The previous settings are the same as you.
Inside the ForEach activity, I use the Additional columns to add the folder name to the datasource.

After copied into a SQL table, the results show as follow:

Update:
My file structure is as follows: You can use expression @concat('FolderA/FolderB/',item().name):

Mixed properties in 'source' column/fields in Azure Data Factory

1 Answers