Intermittent error with $$FILEPATH reserved variable in copy activity

Question

Intermittent error with $$FILEPATH reserved variable in copy activity

Zac 0

Hi

I have a pipeline that copies parquet files from an S3 bucket to an Azure SQL Database.

The Pipeline is parametrized to pass a folder. Another pipeline calls this pipeline 29 times via the execute pipeline task to run the load of 29 folders in parallel. Note. each folder may contain multiple files.

This is run daily via a trigger and every couple of weeks, the pipeline will fail for 2 of the folders (usually the same folders) with the following error:

ErrorCode=ParquetColumnNotFound,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column DSS_RECORDSOURCE does not exist in Parquet file.,Source=Microsoft.DataTransfer.Richfile.ParquetTransferPlugin,'

DSS_RECORDSOURCE is an "additional columns" column using the value $$FILEPATH. Note. The setting recursively has been enabled.

When I rerun the load for the 2 failed files via debug it runs successfully.

1 answer

Your answer

Answer 1

Smaran Thoomu 32,530 Microsoft External Staff Moderator

Hi @Zac

Thank you for reaching out to the community forum with your query.

Based on the error message you provided, it seems that the column "DSS_RECORDSOURCE" does not exist in the Parquet file. This error occurs intermittently for two folders, and you are using the value $$FILEPATH for the "DSS_RECORDSOURCE" column.

This might be the reason for this error could be that the Parquet file in question does not contain the "DSS_RECORDSOURCE" column. You can verify this by checking the schema of the Parquet file using a tool like Parquet Tools. If the column is missing, you can modify the Parquet file to include the "DSS_RECORDSOURCE" column.

Another possible reason for this error could be that the value of $$FILEPATH is not being resolved correctly for the two folders that are failing. You can try to debug this issue by checking the value of $$FILEPATH for the two folders that are failing. You can do this by adding a "Copy Data" activity to your pipeline and setting the source to the folder that is failing. Then, you can add a "Derived Column" activity to your pipeline and set the value of the "DSS_RECORDSOURCE" column to $$FILEPATH. Finally, you can add a "Sink" activity to your pipeline and set the sink to a file in Azure Blob Storage. This will allow you to check the value of $$FILEPATH for the two folders that are failing.

I hope this helps! Let me know if you have any further questions or concerns.

Zac 0 Reputation points

2024-03-06T21:30:04.5933333+00:00

@Smaran Thoomu thanks for the reply."DSS_RECORDSOURCE" does in fact not exist in the source files. This is an additional column generated by the copy task. Please see screenshot attached:

If I rerun the pipeline via debug after a failure it runs successfully. Which suggests it is able to resolve $$FILEPATH.
Smaran Thoomu 32,530 Reputation points Microsoft External Staff Moderator

2024-03-07T10:15:45.3866667+00:00

Hi @Zac

Thank you for the additional information. It's good to know that the issue is not with the source files.

Since the pipeline runs successfully when you rerun it via debug, it's possible that the issue is related to the parallel execution of the pipeline. When you run the pipeline 29 times via the "Execute Pipeline" task, the pipeline is executed in parallel for each folder. This can cause issues with resource contention and may result in intermittent failures.

To resolve this issue, you can try modifying the pipeline to run the copy activity sequentially instead of in parallel. You can do this by removing the "Execute Pipeline" task and instead using a "ForEach" activity to iterate over the folders and run the copy activity for each folder sequentially. This will ensure that the pipeline is not executing in parallel and may help to resolve the intermittent failures.

Additionally, you can try increasing the timeout value for the copy activity to ensure that it has enough time to complete before timing out. You can do this by setting the "Timeout" property for the copy activity to a higher value.

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.

Share via

Intermittent error with $$FILEPATH reserved variable in copy activity

1 answer

Your answer