How to resolve invalid property name error in ADF copy activity?

Mansi Yadav 40

ErrorCode=AdlsGen2OperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ADLS Gen2 operation failed for: Operation returned an invalid status code 'BadRequest'. Account: 'rmsdatalakestracc1mqa'. FileSystem: 'rmsadlscontainer'. Path: 'sharepoint/documents/RMS-MMY/ITBS-Apps/Unique User Experiences – Leading US Based Credit.pdf'. ErrorCode: 'InvalidPropertyName'. Message: 'The property name contains invalid characters.'. RequestId: '71491bd1-201f-0048-74c2-88e256000000'. TimeStamp: 'Sun, 07 Apr 2024 08:05:53 GMT'..,Source=Microsoft.DataTransfer.ClientLibrary,''Type=Microsoft.Azure.Storage.Data.Models.ErrorSchemaException,Message=Operation returned an invalid status code 'BadRequest',Source=Microsoft.DataTransfer.ClientLibrary,'

I'm getting above error in copy data activity in azure data factory. I tried to rename the file name also by removing spaces and - but still getting the same error. In copydata activity I'm copying the file with some metadata associated with it like documentName, last modified date, createdbyUser, etc. It's copying the file but without metadata. Please help me to resolve this issue.

phemanth 7,190 Reputation points Microsoft Vendor

2024-05-20T06:23:03.09+00:00
@Mansi Yadav Thanks for using MS Q&A platform and posting your query.

The error message you’re seeing, InvalidPropertyName, suggests that one or more of the metadata property names you’re trying to copy contains invalid characters. Azure Data Lake Storage Gen2 only allows alphanumeric characters and the dash (-) in property names.

Here are a few steps you can take to resolve this issue:

Check the Metadata Property Names: Ensure that all metadata property names you’re trying to copy are valid according to Azure’s naming rules. They should only contain alphanumeric characters and dashes (-).

Enable Fault Tolerance: Azure Data Factory’s Copy Activity provides a feature called “Fault Tolerance” which allows the copy operation to continue even if some rows are incompatible. You can enable this feature to skip the incompatible metadata and continue with the copy operation.

Log Skipped Data: If you enable session log within the copy activity, you can log the skipped data for review

Use Data Flow Activity: If the issue persists, consider using a Data Flow activity to separate the compatible and incompatible rows before performing the copy operation

Hope this helps. Do let us know If the issue persists
phemanth 7,190 Reputation points Microsoft Vendor

2024-05-21T09:46:16.36+00:00

@Mansi Yadav We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
phemanth 7,190 Reputation points Microsoft Vendor

2024-05-22T10:24:15.7533333+00:00
@Mansi Yadav Fault Tolerance Cost:

Enabling fault tolerance in Azure Data Factory's Copy Activity does not incur any additional charges directly. However, there can be indirect cost implications:

Retries: If rows with incompatible metadata cause retries, it can increase the execution time of your pipeline, potentially leading to higher compute costs.

Data Loss: While fault tolerance skips incompatible rows, it essentially means losing that data. Depending on the importance of this data, you might need additional processing to capture it.

Why You're Getting the Error:

The error "InvalidPropertyName" likely originates from the metadata properties you're defining, not the filename itself. Here's why:

Filename Seems Valid: The filename "Unique User Experiences – Leading US Based Credit.pdf" appears to be within ADLS Gen2 naming conventions.

Focus on Metadata Properties: The error message points towards invalid characters in property names, not filenames.

Possible Reasons and Solutions:

Hidden Characters: Some characters might be invisible in your text editor but still be present. Try pasting the property names into a plain text editor to reveal hidden characters.

Non-Alphanumeric Characters: Double-check the metadata property names for characters beyond letters (a-z, A-Z), numbers (0-9), and hyphens (-). Examples include spaces, commas (,), underscores (_), or special characters (@, #, etc.).

Case Sensitivity: ADLS Gen2 is case-sensitive. Ensure the property names in your definition match exactly with the ones used in the data source.

Removing Invalid Characters:

Here's an example of how to remove invalid characters using a data flow transformation:

Add Data Flow Activity: In your Azure Data Factory pipeline, add a Data Flow activity after the Copy Data activity encountering the error.

Source and Sink: Set the source dataset to the output of the Copy Data activity and the sink to a new dataset for storing the filtered data.

Data Transformation: Use a "Derive Column" transformation within the Data Flow activity.

Regular Expression: In the "Derive Column" transformation, create a new column using a regular expression that removes invalid characters from the metadata property names.

Here's a possible regular expression for removing characters other than alphanumeric and hyphens:

Code snippet

replace(your_metadata_property_name, '[^a-zA-Z0-9-]', '')

Where to Apply the Expression:

Data Flow Transformation: Apply the regular expression within the "Derive Column" transformation in the Data Flow activity. Replace "your_metadata_property_name" with the actual property name.

Dataset Metadata Definition (Optional): If the invalid characters are known beforehand, you can also pre-process the metadata before defining it in the dataset.Fault Tolerance Cost:
Mansi Yadav 40 Reputation points

2024-05-27T06:56:04.2966667+00:00

Is there any solution without using Azure data flow? And as you mentioned that property name should contain alphanumeric characters and dashes (-), what if our filenames is in different language like file name is in german, Spanish, Chinese, etc. Is there any solution that will handle this scenario also?
phemanth 7,190 Reputation points Microsoft Vendor

2024-05-27T09:05:12.69+00:00
@Mansi Yadav Here are some solutions for the "InvalidPropertyName" error in ADF copy activity without using Data Flow and handling filenames with special characters:

1. Pre-process Filenames:

If you have control over the source data, consider renaming files before copying them to ADLS Gen2.

Remove special characters and convert them to alphanumeric characters or hyphens following a consistent naming convention.

2. Use Wildcards (Partially Mitigates Issue):

If filenames follow a pattern with special characters in specific locations, leverage wildcards in the copy activity source settings.

This might not work for filenames with special characters throughout the name.

3. Custom Scripting (Advanced):

If pre-processing or wildcards aren't feasible, explore scripting languages like Python or PowerShell within an Azure Function.

The script can rename files upon arrival in a staging location before copying them to ADLS Gen2, ensuring valid property names.

4. Fault Tolerance with Caution (Data Loss Possible):

As mentioned earlier, enabling fault tolerance in the copy activity allows skipping rows with invalid property names (filenames).

This approach is acceptable if the data loss is minimal or inconsequential. However, be mindful of losing potentially valuable data.

Handling Filenames with Special Characters:

Unfortunately, ADLS Gen2 currently only supports alphanumeric characters and hyphens in property names (filenames). Here are options to consider:

Pre-process or Scripting: As mentioned above, pre-process filenames before copying or use a script to convert special characters to allowed ones.

Alternative Storage: Explore alternative storage solutions that handle filenames with a wider range of characters if ADLS Gen2's limitations are a dealbreaker.

Remember, the best approach depends on your specific scenario and data requirements. Evaluate the trade-offs between data integrity, processing overhead, and potential data loss.

1 answer

Mansi Yadav 40 Reputation points

2024-05-22T01:25:40.66+00:00

Using fault tolerance will add extra cost?

Basically I want to understand why I'm getting this error, filename that I'm copying with the metadata doesn't include any special character :-

Unique User Experiences – Leading US Based Credit.pdf

I'm storing weburl of the file, file name, modified date, created by in the metadata. And I tried to remove special characters ', _, @, etc. but I was still getting the same.

While copying, in the dataset I am passing file name also in the path and it's copying the file but it's giving error for metadata, I have not tried to change the filename, will try that also.

Please explain this with an example and exactly what expression I can use to remove such characters and where I have to add for example in the data set path, filename metadata or both.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

How to resolve invalid property name error in ADF copy activity?

1 answer