How to append file name to Copy activity in Azure Data Factory

Question

I want to transfer data from a CSV file (Source) in Blob storage to an Azure SQL Database table (Sink) using the regular Copy activity in Azure Data Factory (ADF). However, I want to include the file name alongside each entry in the table. Although I've created a table for output with a file name column, this data is not explicitly defined at the column level in the CSV file. Therefore, I need to extract it from the metadata and pair it with the column.

The current mapping appears as shown in the Azure Data Factory image. Initially, I considered using dynamic content to resolve this, but each individual box doesn't allow dynamic content. I'm also exploring the option of using a Pre-copy script but haven't identified how to use it for this specific purpose. What would be the most effective way to address this issue?

Answer 1

Hi @Glasier

Welcome to Microsoft Q&A Platform. Thank you for posting your query.

As I understand you wants to transfer data from a CSV file in Blob storage to an Azure SQL Database table using the Copy activity in Azure Data Factory. However, you want to include the file name alongside each entry in the table. If this is not, please let me know.

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 or else you can give directly as value $$FILENAME to get the filename similarly. @activity('Get Metadata1').output.itemName

Here I am copying the contents of source1_data_preview.csv file to SQL table named samp.

Output

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.