How to fix error code: -2147220872

Question

I get error

Message=Dynamics operation failed with error code: -2147220872,

error message: Invalid character in field 'CheckName': '­'

, hexadecimal value 0x1F, is an invalid character..

,Source=Microsoft.DataTransfer.ClientLibrary.DynamicsPlugin,'

 

Value of the field : :   Uğ­ur

I Tried to put "encoding" UTF-8 but no result so far

Answer 1

Hi @Ben Zeilmaker

Greetings & Welcome to Microsoft Q&A forum! Thanks for posting your query!

Apologies for the delay in response.

Thank you for the additional context. It seems the issue lies in how Dynamics 365 CE interprets and validates certain characters during the data import process, even though these characters worked without issues in your previous setup with SQL Azure. To address your specific points:

Encoding Configuration Dynamics 365 CE expects UTF-8 encoding by default. However, the Copy Data activity may not explicitly handle characters like ğ­ properly if there are invisible control characters (like 0x1F) embedded in the data.

Here's how you can ensure correct encoding throughout the pipeline:

Source Dataset (SQL Azure) - Ensure the source dataset uses UTF-8 encoding. This is typically the default, but you can double-check in the dataset's advanced settings.

Sink Dataset (Dynamics 365 CE) - Since Dynamics 365 CE expects UTF-8, no additional encoding settings are needed in the sink. However, invalid characters must be handled before reaching this step.

For more details, please refer: Manage invalid characters

Please refer to the thread link for similar issue, which might help you in getting some valuable insights.

https://stackoverflow.com/questions/6728329/hexadecimal-value-0x1f-is-an-invalid-character-line-1-position-1

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

I hope this information helps. Please do let us know if you have any further queries.

Thank you.

Answer 2

There is an invalid character (hexadecimal value 0x1F, which is a control character) in the field CheckName of the source data. This can occur when your data contains non-printable or unexpected characters that are not compatible with the Dynamics 365 service, such as control characters or special characters that should not appear in text fields.

You can modify the SQL query to cleanse the CheckName field by replacing or removing any invalid characters before writing to the sink. You can use the REPLACE function in SQL to remove the invalid character:

SELECT TOP 10
REPLACE(CheckName, CHAR(31), '') AS CheckName,
PersonID
FROM YourTable

If the issue is with other control characters or non-printable characters, you can use a more generic solution to remove all control characters:

SELECT TOP 10
REPLACE(CheckName, CHAR(31), '') AS CheckName,
PersonID
FROM YourTable
WHERE CheckName NOT LIKE '%[^ -~]%'

If modifying the SQL query is not feasible, you can use a Data Flow in Azure Data Factory (ADF) to cleanse the data:

1. Add a Data Flow Transformation: Use a Derived Column transformation to remove or replace invalid characters.
2. Expression: In the Derived Column transformation, use an expression similar to:

      replace(CheckName, '­', '')