insert file name in sink table

Question

Hi, i have created a pipeline where it is fetching multiple files from Datalake using Getmetadata Activity. Then we are passing these files into Foreach Activity where we are transforming the data using dataflow and loading into sink table. But we are not able to load the filename into sink table. Can you help me how we can achieve it.

1. We are not using Copy Activity in for each.
2. Created new SetVariable in ForEach which is holding the processing file name.
3. I have created new column name in Source using Source option 'SourceFileName' but not not getting how to pass the variable value in it.

Answer 1

Hi @akash agrawal ,

Thank you for posting query in Microsoft Q&A Platform.

As per my understanding, you would like to have filename column on top of your source columns and load them to sink table. Please correct me if my understanding is wrong.

You can achieve this by having a parameter in data flow to take file name as value. We should have derived column transformation inside dataflow to add new column for file name.

Please check below screenshots for clear idea.

Step1: fileName parameter inside data flow to take file name value from data flow activity.

Step2: Derived column transformation to add new column on top of source columns for file name column.

Step3: In sink transformation make sure to map this new column with desired sink table column.

Step4: Data flow activity, pass value of file in to data flow parameter.

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

---

Please consider hitting Accept Answer. Accepted answers helps community as well.