Message=The attribute 'status' with value '100000006' cannot be converted from original type 'System.String' to target type 'Microsoft.Xrm.Sdk.OptionSetValue'.

Chen, Flora-F1 0

I want to update CRM data with the same value 100000006, but use copy data additional column failed.

@array(json('{"name":"status","value":100000006}'))

Chen, Flora-F1 0 Reputation points

2024-06-18T03:43:45.92+00:00

I think the additional columns default type is string, how can I set a column value with int type?
Smaran Thoomu 16,475 Reputation points Microsoft Vendor

2024-06-18T13:04:09.9233333+00:00
Hi @Chen, Flora-F1

Thanks for the question and using MS Q&A platform.

Based on the error message, it seems that the attribute 'status' in your CRM data is of type 'Microsoft.Xrm.Sdk.OptionSetValue', but you are trying to update it with a string value '100000006'. To resolve this issue, you need to convert the string value to an OptionSetValue object before updating the attribute.

You can use the following code snippet to create an OptionSetValue object with the integer value 100000006:

var optionSetValue = new OptionSetValue(100000006);

Then, you can update the 'status' attribute with this object:

entity.Attributes["status"] = optionSetValue;

Replace 'entity' with the actual entity object you are updating.

Regarding your question about setting a column value with int type, you can specify the data type of the column in the CRM data model as 'Integer' or 'Whole Number' to ensure that the column only accepts integer values. When copying data to this column, make sure to provide integer values only.

I hope this helps. Please let me know if you have any more questions.
Chen, Flora-F1 0 Reputation points

2024-06-19T01:40:31.1766667+00:00

Hi @Smaran Thoomu , thanks for your comment first.

But I want to know how to convert data type in ADF-Copy Data activity-additional column
Smaran Thoomu 16,475 Reputation points Microsoft Vendor

2024-06-19T11:24:50.27+00:00
Hi @Chen, Flora-F1

Thanks for getting back to me.

To convert the data type of a column in Azure Data Factory (ADF) Copy Data activity, you can use the 'Derived Column' transformation. This transformation allows you to add new columns or modify existing columns in your data flow.

Here's how you can use the 'Derived Column' transformation to convert the data type of a column:

Open your data flow in ADF.

Add a 'Derived Column' transformation to your data flow.

In the 'Derived Column' transformation settings, select the column you want to modify.

In the 'Expression' field, use the appropriate expression to convert the data type of the column. For example, if you want to convert a string column to an integer column, you can use the following expression: toInteger(columnName) Replace 'columnName' with the actual name of the column you want to modify.

Save and run your data flow.

I hope this helps. Let me know if you have any more questions.
Smaran Thoomu 16,475 Reputation points Microsoft Vendor

2024-06-24T04:58:26.4+00:00

@Chen, Flora-F1 Just checking in to see if the below answer helped. 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.

1 answer

Smaran Thoomu 16,475 Reputation points Microsoft Vendor

2024-06-21T06:30:04.6+00:00
Hi @Chen, Flora-F1
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue: I want to update CRM data with the same value 100000006, but use copy data additional column failed.

AI ConvertCopy

@array(json('{"name":"status","value":100000006}'))

Solution: I don't have directly solution yet. The suggestion is putting the data into a staging, and it can read as a Int.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Message=The attribute 'status' with value '100000006' cannot be converted from original type 'System.String' to target type 'Microsoft.Xrm.Sdk.OptionSetValue'.

1 answer

Your answer