Create a CE record from a JSON file stored in Azure Blob container

As a part of complex Microsoft Dynamics 365 Data Migration and Integration processes, sources to load data in target D365 CE (CRM) may vary among OneDrive, MS SQL, Azure SQL Database, Azure Blob Storage, SharePoint, etc. In this blog, we will implement a mechanism that reads and processes a JSON file stored in an Azure Blob Container and creates a corresponding record in CE. We will achieve this with the help of an Azure Logic-App.

PRE-REQUISITES

• 1. A Dynamics 365 CE (CRM) instance

• 2. Access to an Azure Storage Account

STEPS

• 1. To begin with, we prepare a simple JSON file, having attributes (logical names) and values of a simple CE Account record.

• 2. We upload the file we created in the previous step to an Azure Blob Container.

• 3. Now comes the real bit. We create a new Logic-App called “D365CE_LoadAccount” and select “When an HTTP request is received” as the trigger.

• 4. Next, in the Logic app designer, add a new action – Get Blob Content, navigate to the JSON file path and select it for the *Blob field.

• 5. Add the next step – Parse JSON. For its *Content field, enter the expression - decodeBase64(body('Get_Blob_Content')['$content']) which essentially reads the actual JSON content stored in the file.

• 6. In the *Schema, enter the following JSON Schema for CE (CRM) Accounts.

  { "properties": { "@@odata.context": { "type": "string" }, "@@odata.etag": { "type": "string" }, "_createdby_value": { "type": "string" }, "_createdbyexternalparty_value": {}, "_createdonbehalfby_value": {}, "_defaultpricelevelid_value": {}, "_masterid_value": {}, "_modifiedby_value": { "type": "string" }, "_modifiedbyexternalparty_value": {}, "_modifiedonbehalfby_value": {}, "_msdyn_billingaccount_value": {}, "_msdyn_preferredresource_value": {}, "_msdyn_salestaxcode_value": {}, "_msdyn_serviceterritory_value": {}, "_msdyn_workhourtemplate_value": {}, "_originatingleadid_value": {}, "_ownerid_value": { "type": "string" }, "_owningbusinessunit_value": { "type": "string" }, "_owningteam_value": {}, "_owninguser_value": { "type": "string" }, "_parentaccountid_value": {}, "_preferredequipmentid_value": {}, "_preferredserviceid_value": {}, "_preferredsystemuserid_value": {}, "_primarycontactid_value": { "type": "string" }, "_slaid_value": {}, "_slainvokedid_value": {}, "_territoryid_value": {}, "_transactioncurrencyid_value": { "type": "string" }, "accountcategorycode": {}, "accountclassificationcode": { "type": "integer" }, "accountid": { "type": "string" }, "accountnumber": { "type": "string" }, "accountratingcode": { "type": "integer" }, "address1_addressid": { "type": "string" }, "address1_addresstypecode": {}, "address1_city": { "type": "string" }, "address1_composite": { "type": "string" }, "address1_country": { "type": "string" }, "address1_county": {}, "address1_fax": {}, "address1_freighttermscode": {}, "address1_latitude": {}, "address1_line1": { "type": "string" }, "address1_line2": {}, "address1_line3": {}, "address1_longitude": {}, "address1_name": {}, "address1_postalcode": { "type": "string" }, "address1_postofficebox": {}, "address1_primarycontactname": {}, "address1_shippingmethodcode": {}, "address1_stateorprovince": { "type": "string" }, "address1_telephone1": {}, "address1_telephone2": {}, "address1_telephone3": {}, "address1_upszone": {}, "address1_utcoffset": {}, "address2_addressid": { "type": "string" }, "address2_addresstypecode": { "type": "integer" }, "address2_city": {}, "address2_composite": {}, "address2_country": {}, "address2_county": {}, "address2_fax": {}, "address2_freighttermscode": { "type": "integer" }, "address2_latitude": {}, "address2_line1": {}, "address2_line2": {}, "address2_line3": {}, "address2_longitude": {}, "address2_name": {}, "address2_postalcode": {}, "address2_postofficebox": {}, "address2_primarycontactname": {}, "address2_shippingmethodcode": { "type": "integer" }, "address2_stateorprovince": {}, "address2_telephone1": {}, "address2_telephone2": {}, "address2_telephone3": {}, "address2_upszone": {}, "address2_utcoffset": {}, "aging30": {}, "aging30_base": {}, "aging60": {}, "aging60_base": {}, "aging90": {}, "aging90_base": {}, "businesstypecode": { "type": "integer" }, "createdon": { "type": "string" }, "creditlimit": {}, "creditlimit_base": {}, "creditonhold": { "type": "boolean" }, "customersizecode": { "type": "integer" }, "customertypecode": {}, "description": { "type": "string" }, "donotbulkemail": { "type": "boolean" }, "donotbulkpostalmail": { "type": "boolean" }, "donotemail": { "type": "boolean" }, "donotfax": { "type": "boolean" }, "donotphone": { "type": "boolean" }, "donotpostalmail": { "type": "boolean" }, "donotsendmm": { "type": "boolean" }, "emailaddress1": { "type": "string" }, "emailaddress2": {}, "emailaddress3": {}, "entityimage": {}, "entityimage_timestamp": {}, "entityimage_url": {}, "entityimageid": {}, "exchangerate": { "type": "integer" }, "fax": { "type": "string" }, "followemail": { "type": "boolean" }, "ftpsiteurl": {}, "importsequencenumber": {}, "industrycode": { "type": "integer" }, "lastonholdtime": {}, "lastusedincampaign": {}, "marketcap": {}, "marketcap_base": {}, "marketingonly": { "type": "boolean" }, "merged": { "type": "boolean" }, "modifiedon": { "type": "string" }, "msdyn_externalaccountid": {}, "msdyn_taxexempt": { "type": "boolean" }, "msdyn_taxexemptnumber": {}, "msdyn_travelcharge": {}, "msdyn_travelcharge_base": {}, "msdyn_travelchargetype": { "type": "integer" }, "msdyn_workorderinstructions": {}, "name": { "type": "string" }, "numberofemployees": { "type": "integer" }, "onholdtime": {}, "opendeals": { "type": "integer" }, "opendeals_date": { "type": "string" }, "opendeals_state": { "type": "integer" }, "openrevenue": { "type": "integer" }, "openrevenue_base": { "type": "integer" }, "openrevenue_date": { "type": "string" }, "openrevenue_state": { "type": "integer" }, "overriddencreatedon": {}, "ownershipcode": { "type": "integer" }, "participatesinworkflow": { "type": "boolean" }, "paymenttermscode": { "type": "integer" }, "preferredappointmentdaycode": {}, "preferredappointmenttimecode": {}, "preferredcontactmethodcode": { "type": "integer" }, "primarysatoriid": {}, "primarytwitterid": {}, "processid": {}, "revenue": { "type": "integer" }, "revenue_base": { "type": "integer" }, "sharesoutstanding": {}, "shippingmethodcode": { "type": "integer" }, "sic": {}, "stageid": {}, "statecode": { "type": "integer" }, "statuscode": { "type": "integer" }, "stockexchange": {}, "teamsfollowed": {}, "telephone1": { "type": "string" }, "telephone2": {}, "telephone3": {}, "territorycode": { "type": "integer" }, "tickersymbol": { "type": "string" }, "timespentbymeonemailandmeetings": {}, "timezoneruleversionnumber": { "type": "integer" }, "traversedpath": {}, "utcconversiontimezonecode": {}, "versionnumber": { "type": "integer" }, "websiteurl": { "type": "string" }, "yominame": {} }, "type": "object" }

• 7. The final step would be to add a new CDS (Common Data Service) Step – Create a new Record (accounts). Get connected to your D365 instance & select the Environment where you want to load the CE Account records in. Map the Account fields to the fields from Parse JSON output. Once mapped, save the Logic-App.

UNIT-TESTING

• 1. We Run the Logic-App and verify if all the steps get executed successfully.

• 2. We navigate to the D365 CE instance and check if the new CE Account record is created successfully.

CONCLUSION

We comprehensively learn in this blog how we can create records in Dynamics 365 BC by reading JSON content stored in a file, hosted on an Azure Blob Container – with the help of a Logic-App. This simple implementation can have multiple applications in Data Integration & Migration process.