I want to copy data from a CSV file (Source) on Blob storage to Azure SQL Database table (Sink) via regular Copy activity but I want to copy also file name alongside every entry into the table. I am new to ADF so the solution is probably easy but I have not been able to find the answer in the documentation and neither on the internet so far.

My mapping currently looks like this (I have created a table for output with the file name column but this data is not explicitly defined at the column level at the CSV file therefore I need to extract it from the metadata and pair it to the column):

For the first time, I thought that I am going to put dynamic content in there and therefore solve the problem this way. But there is not an option to use dynamic content in each individual box so I do not know how to implement the solution. My next thought was to use Pre-copy script but have not seen how could I use it for this purpose. What is the best way to solve this issue?

CodePudding user response：

In Mapping columns of copy activity you cannot add the dynamic content of Meta data.

First give the source csv dataset to the Get Metadata activity then join it with copy activity like below.

You can add the file name column by the Additional columns in the copy activity source itself by giving the dynamic content of the Get Meta data Actvity after giving same source csv dataset.

@activity('Get Metadata1').output.itemName

If you are sure about the data types of your data then no need to go to the mapping, you can execute your pipeline.

Here I am copying the contents of samplecsv.csv file to SQL table named output.

My output for your reference: