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

Question

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

LI CHENG LAM 25

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.

Anil Kumar Raju A 0 Reputation points

2023-06-02T03:44:57.8533333+00:00

Try This !

To extract the current file name from Azure Data Factory and store it in a dataset for use, you can follow these steps:

Create a new pipeline in your Azure Data Factory.

Add a Copy Data activity to the pipeline. This activity will be responsible for copying the file and extracting the file name.

In the Source dataset of the Copy Data activity, configure the connection to your source file and provide the necessary details such as the file path, format, and any required credentials.

In the Sink dataset of the Copy Data activity, configure the connection to your destination file. This will be the location where you want to store the file with the additional Filename column.

Expand the Mapping settings of the Copy Data activity.

Add a new column to the Sink dataset schema by clicking on the "+ Add Column" button. Name this column "Filename" or any desired name.

In the Mapping section, map the Source dataset columns to the Sink dataset columns, including the Filename column. For the Filename column, provide the expression @dataset().SourceFileName in the mapping configuration.

Save and publish the pipeline.

Now, when you run the pipeline, the Copy Data activity will copy the source file and extract the file name. The file will be written to the destination location with an additional Filename column containing the extracted file name.

Note: The @dataset().SourceFileName expression retrieves the name of the current file being processed in the pipeline. Make sure to adjust the expression if your dataset has a different property or path for the file name.

By using this approach, you can extract the file name from the source file and store it as an additional column in your destination file within Azure Data Factory.
QuantumCache 20,676 Reputation points Moderator

2023-06-17T19:12:32.0133333+00:00

Hello @adf001
Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

Answer accepted by question author

1 additional answer

Your answer

Anil Kumar Raju A 0 Reputation points

2023-06-02T03:44:57.8533333+00:00

Try This !

To extract the current file name from Azure Data Factory and store it in a dataset for use, you can follow these steps:

Create a new pipeline in your Azure Data Factory.

Add a Copy Data activity to the pipeline. This activity will be responsible for copying the file and extracting the file name.

In the Source dataset of the Copy Data activity, configure the connection to your source file and provide the necessary details such as the file path, format, and any required credentials.

In the Sink dataset of the Copy Data activity, configure the connection to your destination file. This will be the location where you want to store the file with the additional Filename column.

Expand the Mapping settings of the Copy Data activity.

Add a new column to the Sink dataset schema by clicking on the "+ Add Column" button. Name this column "Filename" or any desired name.

In the Mapping section, map the Source dataset columns to the Sink dataset columns, including the Filename column. For the Filename column, provide the expression @dataset().SourceFileName in the mapping configuration.

Save and publish the pipeline.

Now, when you run the pipeline, the Copy Data activity will copy the source file and extract the file name. The file will be written to the destination location with an additional Filename column containing the extracted file name.

Note: The @dataset().SourceFileName expression retrieves the name of the current file being processed in the pipeline. Make sure to adjust the expression if your dataset has a different property or path for the file name.

By using this approach, you can extract the file name from the source file and store it as an additional column in your destination file within Azure Data Factory.
QuantumCache 20,676 Reputation points Moderator

2023-06-17T19:12:32.0133333+00:00

Hello @adf001
Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!

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

User's image

GetMetadata: Stage

Remember to output Field List: Item Name

User's image

Set Variable: capture the Source FileName

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

User's image

Copy Data Activity:

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

User's image

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

User's image

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?

User's image

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".

User's image

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!!!!

User's image

Answer 2

Joseph G 5

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

QuantumCache 20,676 Reputation points Moderator

2024-03-29T05:08:13.0666667+00:00

Joseph G Glad it is helpful!

Share via

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

1 additional answer

Your answer