将数据从Dynamics CRM加载到SQL Server

问题描述：

无法使用Azure数据工厂从Dynamics CRM到SQL Server获取选项集字符串.

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

我正在使用Azure数据工厂将数据从Dynamics CRM移至SQL DB.我使用fetchXML查询从源(CRM)获取数据.我可以正常获取字符串和guid类型值，而不会出现任何问题.

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.

但是CRM的optionsset字段是Int32类型的(即，我得到的是optionset的值，而不是字符串).

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

如何解决此问题?

答

可能您正在使用

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['industrycode@OData.Community.Display.V1.FormattedValue'];

如果您无法执行此操作，则最好在SQL中转储另一个名为stringmap的表，该表将存储系统中的所有选择列表选项.

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

了解详情

将数据从Dynamics CRM加载到SQL Server

相关推荐