Forum Discussion
MS Fabric Data Pipeline Copy Data Activity Additional Column Value automatic change error
Hi PrasoonSur,
Thank you for reaching out to Microsoft Fabric Community.
Thank you nilendraFabric for the prompt response.
Here the use of $$FILENAME in the Additional columns section of the Copy activity in Microsoft Fabric pipelines is currently not supported the same way it is in Azure Data Factory. This is why you are seeing the value change to Custom with an error.
Fabric’s pipeline UI blocks reserved tokens like $$FILENAME from being used in custom fields. Even if accepted, they are not passed correctly downstream especially to Dataflows.
- Since you have many files, using ForEach will slow down over time. Instead use a single Copy Activity that loads all files into a Staging Lakehouse table.
- Dynamically add the filename by adjusting your source/file path logic
- After loading, use a Dataflow to read from the staging table and apply any mapping or transformations required using the filename column.
PySpark notebook can also ingest large number of files in parallel with filenames included, if performance is critical and you are ingesting many files, use PySpark.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Super UserHi PrasoonSur
I think you can't pass $$FILENNAME like this
workaround is to use the Get Metadata activity to retrieve filenames and pass them to the Copy Data Activity using variables or expressions
Hi nilendraFabric ,
Thanks for the quick reply. I tried the Get Metadata activity followed by ForEach activity for copy data, as I have around 45000 files at Azure Blob Storage. And the files increase at the rate of 2000 files per day. ForEach activity is taking too much time to complete the run, is there any work around to make it faster?
Would the PySpark run be faster?
Thanks for your support.
v-achippaCommunity Support
Hi PrasoonSur,
Thank you for reaching out to Microsoft Fabric Community.
Thank you nilendraFabric for the prompt response.
Here the use of $$FILENAME in the Additional columns section of the Copy activity in Microsoft Fabric pipelines is currently not supported the same way it is in Azure Data Factory. This is why you are seeing the value change to Custom with an error.
Fabric’s pipeline UI blocks reserved tokens like $$FILENAME from being used in custom fields. Even if accepted, they are not passed correctly downstream especially to Dataflows.
- Since you have many files, using ForEach will slow down over time. Instead use a single Copy Activity that loads all files into a Staging Lakehouse table.
- Dynamically add the filename by adjusting your source/file path logic
- After loading, use a Dataflow to read from the staging table and apply any mapping or transformations required using the filename column.
PySpark notebook can also ingest large number of files in parallel with filenames included, if performance is critical and you are ingesting many files, use PySpark.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thanks and regards,
Anjan Kumar Chippa
Hi v-achippa ,
I made the following changes and now the filename reflects in the output.
1. Changed file path type from file path to wildcard path and used wildcard path to point to all CSV files
2. Added additional columns. Changed the Column name from Source.Name to filename (Not sure if this would have helped). Even though the error was thrown as shown below, preview was correct.
3. Then in the mapping, I removed inconsistent columns (Column D & E)
Then it was working well. Not sure which step solved the issue, but for now it seems to be working.
If I pinpoint the issue, I will be posting it here, but thanks for everyone's help v-achippa nilendraFabric .