Microsoft Dynamics 365 | Integration, Dataverse...

Returning formatted value from the azure data factory dynamics connector

(0) Share

Report

Posted on by Cteague

Hi All,

I have a linked service and data set created in azure data factory, the data pulls though fine in to a sql table however I am receiving the data in the wrong format as the underlying codes, does anyone know what I need to do to receive the formatted values instead?

I am using the dynamics connector but have also used the dataverse connector and receive the same results.

Thanks in advance

Chris

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

Hi Cteague,

Could you provide the sample data and it's format in the dataverse and which format you want to save in SQL table?

Was this reply helpful? Yes No
Cteague 5 on at

Like (0)

Report

Hi thank you and yes no problem.

For example the data flows through as

dw_biodiv_q2v2

627160002

627160000

627160002

627160002

NULL

627160002

627160002

627160002

But 627160002 refers to Yes and 627160000 as No and I need to see Yes and No not the code

I believe I need to be able to do the following and call the formatted value from the below from the webapi

GET [Organization URI]/api/data/v9.1/accounts?$select=name,donotpostalmail,accountratingcode,numberofemployees,revenue

&$top=1 HTTP/1.1

Accept: application/json

OData-MaxVersion: 4.0

OData-Version: 4.0

Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

But have tried many options no luck so far

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi Cteague,

If you are using Data Flow, you could create DerivedColumn transformation to change 627160002 to Yes with the expression like this: iif(dw_biodiv_q2v2 = 627160002,Yes).

If you directly use Copy Data activity, you could create a Stored procedure activity and use it to change 627160002/627160000.

If you believe that you could get the formatted data from the webapi, you could create a Copy Data activity which source is Rest and sink is your SQL table. However, you need to get token first.

Was this reply helpful? Yes No