How to extract the current file name from azure data factory and store the value in a dataset for use

Question

I have a dataset which name as TMK_20230105_010112.csv and being use as the source file in data flow, I would like to extract the file name (TMK_20220105_010112) out and put it back to the source file as a additional column - Filename.

Answer 1

Hello @adf001 Thanks for posting this question on this forum!

The ADF Pipeline Looks like below :

First create Pipeline Variable called "FileName"

GetMetadata-->SetVariable-->CopyData

GetMetadata: Stage

Remember to output Field List: Item Name

Set Variable: capture the Source FileName

@activity('Get Metadata Of File').output.itemName

Copy Data Activity:

Source: --> Add New Column--> Put the name as -->"FileName" and the Custom value as --> @variables('FileName')

Pro Tip: Source--> Click Preview Data --> Provide the Variable Name for the FileName as for testing purpose ... example: Salesheader in my example!

And you will see the Preview of Data with the Newly Added Column "FileName", this is just a quick testing to see the source data is enhanced or not?

Sink: Is as normal , no fancy settings, so I am skipping the image!

Mapping: We see the 'Import Schema' has done the mapping along with the Additional Column Name called "FileName".

Output: Run the ADF pipeline and see the output file having the additional column !!!!

We now see the Output file has a new Column added for the FileName!!!!

Answer 2

Wow... this is extremely helpful. Very well explained and laid out!