Loading Data from Dynamics CRM to SQL Server

1
votes

Not able to get optionset string from Dynamics CRM to SQL Server using Azure data factory.

I am using Azure data factory to move data from Dynamics CRM to SQL DB. I used fetchXML query to get the data from source (CRM). I am able to get normal string and guid type values without any issue.

But the optionset field from CRM is coming as Int32 type (ie, I am getting the value of optionset, not the string).

How can I fix this issue?

2
dynamics-crmazure-data-factoryfetchxml
Could you try something like in the link, yes it is sql but could give you hint. community.dynamics.com/365/b/crmmemories/archive/2017/05/02/… – AnkUser
How do you run your fetchXML query? Do you use any code for it? – André Cavaca
I am using this approach – Johnson Thomas
@JohnsonThomas will my solution works for you? I never worked on ADF.. but this is how we do in SSRS, PowerBI, etc – Arun Vinoth - MVP
@ArunVinoth Currently using the stringmap table approach. This required manually adding all the optionset id and string to stringmap table once. But for now I did not get any other solution. Thanks a lot for your help. – Johnson Thomas

2 Answers

0
votes

I'm going to have to sync the stringmaps entity too as I exceeded the link-entity limit for a given FetchXML query.

The following will bring in the textual value of an OptionSet selection.

    <link-entity name="stringmap" from="attributevalue" to="____" visible="false" link-type="outer" alias="____">
        <filter type="and">
            <condition attribute="objecttypecode" operator="eq" value="___"/>
        </filter>
        <attribute name="value"/>
    </link-entity>

to should be the name of the OptionSet column on the host/root entity

alias whatever you want to call the column in the output. I used the same value as to

value This is the object type code for your host/root entity. It is an integer value.

0
votes

Probably you are using this approach to get the fetchxml resultset as Dynamics source to dumping into SQL using Azure Data factory. The problem you are facing is unable to consume the formatted text value for that picklist option.

We will do consume the formatted value using below syntax in code: Reference

//Option set
var industrycodeValue = accountDetails['industrycode'];

var industrycodeTextValue = accountDetails['[email protected]'];

If you cannot do such thing, then it’s better to dump another table in your SQL called stringmap which will store all the picklist options across the system.

Then you can inner join both tables to get the necessary data.

select INC.TicketNumber[Case ID],SMT.Value[Status Name], SMT.AttributeValue[Status Value]
from incident as INC inner join StringMap as SMT
on INC.StatusCode = SMT.AttributeValue
where SMT.AttributeName='statuscode' and SMT.ObjectTypeCode=112

Read more