BizTalk Server 2013 R2 Dynamics CRM Online / On Premise CRUD Operations (Part 2)
Continuation from previous
- This article is a continuation from the Part 1.
- BizTalk Server 2013 R2 Dynamics CRM Online / On Premise CRUD Operations (Part 1)
- Integration Between Microsoft BizTalk Server 2013 & Microsoft Dynamics CRM Online 2016
Best Practices that you can follow
- For the below BizTalk artifacts, you can follow the below best practices for CRM Integration.
Schemas
- From source schema type filter all the required fields to a flat canonical schema.
- Use canonical schema as a source to your maps, when you work with CRM operations.
- The canonical schema helps you to link directly for you XSLT parameters.
Mapping
- Best to use Custom XSLT for creating CRM Operation Requests.
- When you use Custom XSLT, use the if condition for every attribute that you are going to use.
- When you are using the fetch expression, use if conditions to include to avoid passing empty values, otherwise it leads to getting all the records from that entity.
- Use Good naming conventions for your XSLT template names, input parameters.
Orchestration
- Make use of multipart messages for message types using service schemas.
- When you work with multiple CRM entities, it is necessary to work with multipart messaging, Transformations.
- You can create multipart maps, using orchestration editor.
- Use meaningful naming convention for every orchestration shapes, variables, messages, Types.
- Before sending any request to CRM, it’s better to check the Attributes KeyValuePairOfstringanyType count, so that you can ensure you are not sending empty requests.
- Before sending fetch query request, it’s better to check the source field data is not null.
- In a fetch query / expression, if you send empty field condition value, instead of getting expected record, this will cause to get all the records from that entity.
- Use all better exception handling, for every Operation that you perform.
Demo: Using BizTalk Solution Artifacts
- The CRM schemas can be downloaded from the CRM Online SDK, under the below folder path, Add to BizTalk Project, Build. .\SDK\Schemas\CRMBizTalkIntegration
- For this article demo, download the entire solution from MSDN Code Gallery.
- The solution has the below BizTalk artifacts for this article demonstrations.
- BizTalk CRM Organization Service Schemas, Reused from SDK.
- CRUD C# Console Application
- CRUD BizTalk Application
- Schemas
- Maps
- Pipelines
- Orchestration
- Create - Creates a new record in Dynamics CRM.
- Update - Updates an existing record in Dynamics CRM, with reference to Email ID.
- Delete - Deletes an existing record in Dynamics CRM, with reference to Email ID.
- Retrieve Multiple - Retrieves all the records from Registration Page entity.
Steps to Configure the BizTalk Application
- Download, Extract, Build the solution.
- Update the server name for all BizTalk projects Under the Deployment Section.
- Deploy BizTalk.CRMOnline.CRMSchemas
- Deploy BizTalk.CRMOnline.CRUD
- Import the bindings which has Receive, Send ports, Bindings can be copied from the Zip Folder.
- Start the application.
- Drop the files, Observe the response folder to see Response for all operation.
- Verify the CRM Online records for every operation that you perform Create, Update, Delete.
- For Retrieve Multiple for Registration Page entity, Check the response, Response file will have all the record details.
BizTalk Artifacts Solution
- You have sample Registration type as a source file type, with operation field to specify the CRM Operation like CRUD.
- The decision will be happening in Orchestration the corresponding operation is invoked while processing the file, response, an exception is sent out to the folder.
- Custom pipelines with XML Disassembler, Assembler used in Receive, Send ports.
- CRM Schemas can be extracted from CRM Online SDK 2015.
Fig – CRM CRUD BizTalk Project Solution snapshot
This article focuses on the below operations
- The article focuses on the below CRUD Operations.
Create Operation
- Map1 - Mapping from Registration to CRM Retrieve Multiple Request to get the Country GUID, GUID will be used for entity lookup reference for creating a new record.
- Map2 - Mapping from Registration, Retrieve Multiple Response to CRM Create Request schema type for creating a new record.
Custom XSLT is used in this case, to handle all the different CRM data types in an easy way.
Fig – Map1, using the country name from source, we need to retrieve CountryPage entity’s record GUID.
Fig – Map2 Create Request from source schema, Retrieve Multiple responses with country record GUID.
Update Operation
- Map1 - Mapping from Registration to CRM Retrieve Multiple Request to get the GUID from Country, GUID will be used for entity lookup reference for creating a new record.
- Map2 - Mapping from Registration to CRM Retrieve Multiple Request to get the GUID from Email, Guild will be used for entity lookup reference for creating a new record.
- Map3 - Mapping from Registration, Retrieve Multiple Response from Country, Retrieve Multiple Response from Email, to CRM Update Request schema type for updating the record using Email GUID.
- Custom XSLT is used in this case, to handle all the different CRM data types in an easy way.
Fig – Map1, using the country name from source, we need to retrieve CountryPage entity’s record GUID to pass in Update Request.
Fig – Map2, using EmailID from source, we need to retrieve RegistrationPage entity’s record GUID to pass in the Update request.
Fig - Map3, Update Request from Source schema, Retrieve Multiple Country, EmailID response to update the CRM record using GUID.
Delete Operation
- Map1 - Mapping from Registration to CRM Retrieve Multiple Request to get the GUID from Email, GUID will be used to map to Delete Request.
- Map2 - Mapping from Retrieve Multiple Response to CRM Delete Request schema type for deleting the record.
- Direct Mapping for this case, as there are no much parameters.
Fig – Map1, using EmailID from source, we need to retrieve RegistrationPage entity’s record GUID to pass in Delete Request to delete the record.
Fig – Map2, Delete Request from Retrieve Multiple EmailID response for deleting the CRM record using GUID, Entity Name.
Retrieve Multiple Operation
- Mapping from Registration to CRM Retrieve Multiple Request.
- Fetch Query will have hard coded in the map to retrieve all the records.
- Custom XSLT is used in this case.
Fig – Mapping with Hardcoded XSLT to retrieve the CRM Records with specified entity.
Orchestration Flow for every operation
- Different flow based on the Operation type from the Registration schema, XML Instance.
- CRM Response, Exception will be sent to a File folder.
Fig- CRM CRUD Operations using BizTalk Orchestration Process.
BizTalk Mapping XSLT for CRUD Operations
- XSLT Templates for Create, Update, Retrieve Multiple Operations.
- For Delete Operation its direct mapping.
Create Template
- Custom Create Operation XSLT Template for mapping to CRM Create Request.
- You will be using mostly the **b:Attributes **section, to include all the attributes / fields in the request.
- The section **b:LogicalName **to inform the Entity name.
<xsl:template name="BizTalk.CRMOnline.Create">
<xsl:param name="RegistrationLogicalName"/>
<xsl:param name="CountryLogicalName"/>
<xsl:param name="FName"/>
<xsl:param name="LName"/>
<xsl:param name="Age"/>
<xsl:param name="Gender"/>
<xsl:param name="IsMarried"/>
<xsl:param name="Mobile"/>
<xsl:param name="Email"/>
<xsl:param name="CountryGuid"/>
<Create xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">
<entity xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<b:Attributes xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<xsl:if test="$LName">
<b:KeyValuePairOfstringanyType>
<c:key>sms_firstname</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$FName"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$FName">
<b:KeyValuePairOfstringanyType>
<c:key>sms_lastname</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$LName"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Mobile">
<b:KeyValuePairOfstringanyType>
<c:key>sms_mobile</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$Mobile"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Email">
<b:KeyValuePairOfstringanyType>
<c:key>sms_emailid</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$Email"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Age">
<b:KeyValuePairOfstringanyType>
<c:key>sms_age</c:key>
<c:value i:type="d:int" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$Age"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$IsMarried">
<b:KeyValuePairOfstringanyType>
<c:key>sms_ismarried</c:key>
<c:value i:type="d:boolean" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$IsMarried"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Gender='Male'">
<b:KeyValuePairOfstringanyType>
<c:key>sms_gender</c:key>
<c:value i:type="b:OptionSetValue">
<b:Value>180610000</b:Value>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Gender='Female'">
<b:KeyValuePairOfstringanyType>
<c:key>sms_gender</c:key>
<c:value i:type="b:OptionSetValue">
<b:Value>180610001</b:Value>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$CountryGuid">
<b:KeyValuePairOfstringanyType>
<c:key>sms_country</c:key>
<c:value i:type="b:EntityReference">
<b:Id>
<xsl:value-of select="$CountryGuid"/>
</b:Id>
<b:KeyAttributes xmlns:d="http://schemas.microsoft.com/xrm/7.1/Contracts"/>
<b:LogicalName>
<xsl:value-of select="$CountryLogicalName"/>
</b:LogicalName>
<b:Name i:nil="true"/>
<b:RowVersion i:nil="true"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
</b:Attributes>
<b:EntityState i:nil="true"/>
<b:FormattedValues xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<b:Id>00000000-0000-0000-0000-000000000000</b:Id>
<b:KeyAttributes xmlns:c="http://schemas.microsoft.com/xrm/7.1/Contracts" xmlns:d="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<b:LogicalName>
<xsl:value-of select="$RegistrationLogicalName"/>
</b:LogicalName>
<b:RelatedEntities xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<b:RowVersion i:nil="true"/>
</entity>
</Create>
</xsl:template>
XSLT Snippet - Create Request to create the records in CRM.
Update Template
- Custom Update Operation XSLT Template for mapping to CRM Update Request.
- You will be using mostly the b:Attributes section, to include all the attributes / fields in the request.
- The section b:LogicalName to inform the Entity name.
- The section **b:Id **to inform the GUID or the record that you are going to update.
<xsl:template name="BizTalk.CRMOnline.Update">
<xsl:param name="RegistrationLogicalName"/>
<xsl:param name="CountryLogicalName"/>
<xsl:param name="UpdateGUID"/>
<xsl:param name="FName"/>
<xsl:param name="LName"/>
<xsl:param name="Age"/>
<xsl:param name="Gender"/>
<xsl:param name="IsMarried"/>
<xsl:param name="Mobile"/>
<xsl:param name="Email"/>
<xsl:param name="CountryGuid"/>
<Update xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">
<entity xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<b:Attributes xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<xsl:if test="$LName">
<b:KeyValuePairOfstringanyType>
<c:key>sms_firstname</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$FName"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$FName">
<b:KeyValuePairOfstringanyType>
<c:key>sms_lastname</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$LName"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Mobile">
<b:KeyValuePairOfstringanyType>
<c:key>sms_mobile</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$Mobile"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Email">
<b:KeyValuePairOfstringanyType>
<c:key>sms_emailid</c:key>
<c:value i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$Email"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Age">
<b:KeyValuePairOfstringanyType>
<c:key>sms_age</c:key>
<c:value i:type="d:int" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$Age"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$IsMarried">
<b:KeyValuePairOfstringanyType>
<c:key>sms_ismarried</c:key>
<c:value i:type="d:boolean" xmlns:d="http://www.w3.org/2001/XMLSchema">
<xsl:value-of select="$IsMarried"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Gender='Male'">
<b:KeyValuePairOfstringanyType>
<c:key>sms_gender</c:key>
<c:value i:type="b:OptionSetValue">
<b:Value>180610000</b:Value>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$Gender='Female'">
<b:KeyValuePairOfstringanyType>
<c:key>sms_gender</c:key>
<c:value i:type="b:OptionSetValue">
<b:Value>180610001</b:Value>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
<xsl:if test="$CountryGuid">
<b:KeyValuePairOfstringanyType>
<c:key>sms_country</c:key>
<c:value i:type="b:EntityReference">
<b:Id>
<xsl:value-of select="$CountryGuid"/>
</b:Id>
<b:KeyAttributes xmlns:d="http://schemas.microsoft.com/xrm/7.1/Contracts"/>
<b:LogicalName>
<xsl:value-of select="$CountryLogicalName"/>
</b:LogicalName>
<b:Name i:nil="true"/>
<b:RowVersion i:nil="true"/>
</c:value>
</b:KeyValuePairOfstringanyType>
</xsl:if>
</b:Attributes>
<b:EntityState i:nil="true"/>
<b:FormattedValues xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<b:Id>
<xsl:value-of select="$UpdateGUID"/>
</b:Id>
<b:KeyAttributes xmlns:c="http://schemas.microsoft.com/xrm/7.1/Contracts" xmlns:d="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<b:LogicalName>
<xsl:value-of select="$RegistrationLogicalName"/>
</b:LogicalName>
<b:RelatedEntities xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic"/>
<b:RowVersion i:nil="true"/>
</entity>
</Update>
</xsl:template>
XSLT Snippet - Update Request to update the CRM Record using GUID.
Retrieve Multiple Template
- Custom Retrieve Multiple Operation XSLT Template for mapping to CRM Retrieve Multiple Request.
- You can specify those column names on which you are interested in using.
- Specifying all the columns leads response message size to increase.
Parameters value passed for the below
sms_registrationpage
sms_emailid
Email ID field from source schema
<xsl:template name="BizTalk.CRMOnline.RetrieveMultiple.EmailID">
<xsl:param name="LogicalName"/>
<xsl:param name="LookUpColumn"/>
<xsl:param name="LookUpValue"/>
<ns0:RetrieveMultiple xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns0:query xsi:type="ns4:FetchExpression">
<ns4:Query>
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="<xsl:value-of select="$LogicalName"/>">
<attribute name="<xsl:value-of select="$LookUpColumn"/>" />
<filter type="and">
<condition attribute="<xsl:value-of select="$LookUpColumn"/>" operator="eq" value="<xsl:value-of select="$LookUpValue"/>" />
</filter>
</entity>
</fetch>
</ns4:Query>
</ns0:query>
</ns0:RetrieveMultiple>
</xsl:template>
XSLT Snippet - Retrieve Multiple to fetch the record using Email ID from an entity using the fetch expression
Parameters value passed for the below
sms_countrypage
sms_countryname
Country field from source schema
<xsl:template name="BizTalk.CRMOnline.RetrieveMultiple.CountryName">
<xsl:param name="LogicalName"/>
<xsl:param name="LookUpColumn"/>
<xsl:param name="LookUpValue"/>
<ns0:RetrieveMultiple xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns0:query xsi:type="ns4:FetchExpression">
<ns4:Query>
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="<xsl:value-of select="$LogicalName"/>">
<attribute name="<xsl:value-of select="$LookUpColumn"/>" />
<filter type="and">
<condition attribute="<xsl:value-of select="$LookUpColumn"/>" operator="eq" value="<xsl:value-of select="$LookUpValue"/>" />
</filter>
</entity>
</fetch>
</ns4:Query>
</ns0:query>
</ns0:RetrieveMultiple>
</xsl:template>
XSLT Snippet - Retrieve Multiple to fetch the record using Country Name from an entity using the fetch expression.
- Custom Retrieve Multiple XSLT Template is to retrieve all the columns from an entity.
Parameters value passed for the below
sms_registrationpage
<xsl:template name="BizTalk.CRMOnline.RetrieveMultiple.RegistrationPage">
<xsl:param name="LogicalName"/>
<ns0:RetrieveMultiple xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns3="http://schemas.microsoft.com/xrm/2011/Metadata" xmlns:ns5="http://schemas.microsoft.com/2003/10/Serialization/" xmlns:ns1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" xmlns:ns4="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns0:query xsi:type="ns4:FetchExpression">
<ns4:Query>
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="<xsl:value-of select="$LogicalName"/>">
<attribute name="sms_registrationpageid" />
<attribute name="sms_firstname" />
<attribute name="createdon" />
<attribute name="sms_mobile" />
<attribute name="sms_lastname" />
<attribute name="sms_ismarried" />
<attribute name="sms_gender" />
<attribute name="sms_emailid" />
<attribute name="createdby" />
<attribute name="sms_country" />
<attribute name="sms_age" />
<attribute name="modifiedon" />
<order attribute="sms_firstname" descending="false" />
</entity>
</fetch>
</ns4:Query>
</ns0:query>
</ns0:RetrieveMultiple>
</xsl:template>
XSLT Snippet - Retrieve Multiple to fetch an entity with all the columns using fetch expression
Testing the Sample CRUD
- The below steps shows with all the input, an output for the operation performed visually.
Sample Files
- Folder category.
Fig – Sample Classification folder for all operation
Fig – Sample files for all operation
Create
- Copy the sample files from the create sample folder.
- Drop the file and look for the response.
- If there is any fault, fault response will be in Faults folder.
Fig – Create Operation Using BizTalk
- In CRM new records.
Fig – Create Operation using BizTalk CRM Output
- When you re-drop the same file, we should expect some exception.
- Reason: we made Email ID field as primary key, to keep records unique. (Use of Key is not mandatory I am using to avoid duplicates).
Fig – Create Operation using BizTalk Primary Key Unique
Retrieve Multiple
- To Initiate the request, using the below request.
Fig - Source Request, Just to Initiate the request.
Fig - Source Request, CRM Retrieve Multiple Response
Fig - CRM Response Entities in XML format.
Update
- Update Operation based on Email ID. (Email ID is used like where clause).
Fig – CRM Response, Record Updated Output, and Request used.
Delete
- Delete Operation based on Email ID. (Email ID is used like where clause).
Fig – CRM Response and Request used.
Fig – CRM Record Deleted Output.
Conclusions
- This article helps you in understanding the CRUD Operations using BizTalk Server Application.
See Also
- Integration Between Microsoft BizTalk Server 2013 & Microsoft Dynamics CRM Online 2016
- BizTalk Server 2013 R2 Dynamics CRM Online / On Premise CRUD Operations (Part 1)
- Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.