Sunday, 23 October 2016

BizTalk Server - Dynamics CRM Online / On Premise CRM Paging Cookie


Problem Scenario

·          While communicating with CRM Entities using Retrieve Multiple operation using request response pattern there  are some scenarios where you need to process bulk request queries like using pattern matching Contains Data,  Not null, etc.

       These query results bulk data to BizTalk as a response in XML Format with all the details.  

        Scenario 1: Assume you need to process 10,000 records by interacting with CRM.
             OR
        Scenario 2: Single fetch Query, Expression can get you up to 5,000 records.

       Handling these huge file in BizTalk at once could result in performance impact. Now think of scenario when  you have to process multiples request of such huge records. 5000 records XML data can be up to 500 MB plus,  depending on the data you retrieve like column / attributes.

Solution


·         In such scenario using the below pattern will help you to process the limited records with multiple calls without overloading BizTalk with huge data.

·         There are multiple ways with which you can query CRM
  1. Retrieve Operation
  2. Retrieve Multiple Operation

·         Some Points one needs to consider when using Retrieve, Retrieve Multiple Operation for retrieving data from CRM.


         Retrieve Operation


  •           Retrieves Only one record using the parameters like Entity Name, GUID, Columns of the record.
  •           Using this we cannot retrieve multiple records or related data.
  •          This will not support for using where clauses.     
  •           Where there is a scenario to retrieve single record, with known GUID we could use this approach.




                                                               Fig – Retrieve Operation

The below request will return you all the column for the GUID matching record.

 <Retrieve xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
      <entityName>account</entityName>  
      <id>1D30CA6E-E355-E511-80F2-C4346BC5A780</id>  
      <columnSet xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:AllColumns>true</b:AllColumns>  
       <b:Columns xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
         <c:string>name</c:string>  
       </b:Columns>  
      </columnSet>  
   </Retrieve>  

                                                                  Sample XML Retrieve Request

Retrieve Multiple Operation


  • Retrieve Multiple supports many other options / several overloads like QueryExpressionFetchExpression,QueryByAttribute.
  • We can use this operation to retrieve multiple, related entity records in a single request using where clauses, with many other parameters filtering options.
  • However, it has some limitation like single Retrieve Multiple Request can get you some limited records up to 5000, this is configurable in CRM On premise but not in Online.





                                              Fig – Retrieve Multiple Operation

Sample Retrieve Multiple Request from BizTalk to CRM in two formats FetchExpression, QueryExpression for the same query.

      The below sample basic query will return all the entity records with all the columns / attributes specified, maximum entity records can be 5000 as it’s a limit at CRM.

 <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" 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">  
      <query i:type="b:FetchExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:Query>  
       &lt;fetch version=&quot;1.0&quot; output-format=&quot;xml-platform&quot; mapping=&quot;logical&quot; distinct=&quot;false&quot; &gt;  
      &lt;entity name=&quot;sms_registrationpage&quot;&gt;  
      &lt;attribute name=&quot;sms_registrationpageid&quot; /&gt;  
      &lt;attribute name=&quot;sms_firstname&quot; /&gt;  
      &lt;attribute name=&quot;sms_mobile&quot; /&gt;  
      &lt;attribute name=&quot;sms_lastname&quot; /&gt;  
      &lt;attribute name=&quot;sms_ismarried&quot; /&gt;  
      &lt;attribute name=&quot;sms_gender&quot; /&gt;  
      &lt;attribute name=&quot;sms_emailid&quot; /&gt;  
      &lt;attribute name=&quot;sms_country&quot; /&gt;  
      &lt;attribute name=&quot;sms_age&quot; /&gt;  
      &lt;order attribute=&quot;sms_firstname&quot; descending=&quot;false&quot; /&gt;  
      &lt;/entity&gt;  
      &lt;/fetch&gt;  
       </b:Query>  
      </query>  
    </RetrieveMultiple>  

                             Fig- Retrieve Multiple Using FetchExpression

 <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:ns2="http://schemas.microsoft.com/2003/10/Serialization/Arrays" xmlns:ns0="http://schemas.microsoft.com/xrm/2011/Contracts/Services" 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">  
      <query i:type="b:QueryExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
       <b:ColumnSet>  
         <b:AllColumns>false</b:AllColumns>  
         <b:Columns xmlns:e="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
          <e:string>sms_registrationpageid</e:string>  
          <e:string>sms_firstname</e:string>  
          <e:string>sms_mobile</e:string>  
          <e:string>sms_lastname</e:string>  
          <e:string>sms_ismarried</e:string>  
          <e:string>sms_gender</e:string>  
          <e:string>sms_emailid</e:string>  
          <e:string>sms_country</e:string>  
          <e:string>sms_age</e:string>  
         </b:Columns>  
       </b:ColumnSet>  
       <b:Criteria>  
         <b:Conditions/>  
         <b:FilterOperator>And</b:FilterOperator>  
         <b:Filters/>  
       </b:Criteria>  
       <b:Distinct>false</b:Distinct>  
       <b:EntityName>sms_registrationpage</b:EntityName>  
       <b:LinkEntities/>  
       <b:Orders>  
         <b:OrderExpression>  
          <b:AttributeName>sms_firstname</b:AttributeName>  
          <b:OrderType>Ascending</b:OrderType>  
         </b:OrderExpression>  
       </b:Orders>  
       <b:PageInfo>  
         <b:Count>0</b:Count>  
         <b:PageNumber>1</b:PageNumber>  
         <b:PagingCookie i:nil="true"/>  
         <b:ReturnTotalRecordCount>false</b:ReturnTotalRecordCount>  
       </b:PageInfo>  
       <b:NoLock>false</b:NoLock>  
      </query>  
    </RetrieveMultiple>  

                             Fig- Retrieve Multiple Request Using QueryExpression

        

       CRM Paging Cookie


There can be some scenario where you need to query the CRM records and process the response data records in set of records. You can use the paging cookie feature to make paging in an application faster for large datasets. When you use the paging cookie feature when querying a set of records, the result contains a value for the paging cookie. To improve system performance, you can then pass that value when you retrieve the next set of records. 

  Scenario 1:

Assume that an entity has 10,000 records and BizTalk has to query to get those records and process individual records within a loop. Here we know that CRM will return maximum of 5000 records in single response. In this case we can split it up with two requests, set 1st to get 1st 5000, 2nd set to get next another 5000 records and then process them in BizTalk. The downside here will be that file with 5000 records will be size approx 300 – 500 MB depending on the attributes selections.


Scenario 2:

Assume, an entity has 10,000 records, BizTalk has to process them individually. Here the requirement is to query a set of 1000 records at a time, process them in BizTalk and then go for next set of batch of records and so on. So BizTalk has to query CRM 10 times to get 1000 records every time to process 10,000 records. Going with this approach there will be less load in BizTalk as we restricted the batch size.

The approach discussed in Scenario 2 is more performance oriented and yield better results.



Demo


                                                          Fig – CRM Entity Records total 4 records.


·         Page Cookie using FetchExpression



                                                                              Fig – Mapping to FetchExpression

  
 <xsl:template name="FetchExpression">  
   <xsl:param name="Count" />  
   <xsl:param name="pageIndex"/>  
   <xsl:param name="pageCookie" />  
   <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
    <query i:type="b:FetchExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
     <b:Query>  
      &lt;fetch version=&quot;1.0&quot; output-format=&quot;xml-platform&quot; mapping=&quot;logical&quot; distinct=&quot;false&quot; count=&quot;<xsl:value-of select="$Count"/>&quot; <xsl:if test="$pageIndex">  
       page="<xsl:value-of select="$pageIndex"/>"  
      </xsl:if> <xsl:if test="$pageCookie">  
       paging-cookie="<xsl:value-of select="$pageCookie"/>"  
      </xsl:if> &gt;  
      &lt;entity name=&quot;sms_registrationpage&quot;&gt;  
      &lt;attribute name=&quot;sms_registrationpageid&quot; /&gt;  
      &lt;attribute name=&quot;sms_firstname&quot; /&gt;  
      &lt;attribute name=&quot;sms_mobile&quot; /&gt;  
      &lt;attribute name=&quot;sms_lastname&quot; /&gt;  
      &lt;attribute name=&quot;sms_ismarried&quot; /&gt;  
      &lt;attribute name=&quot;sms_gender&quot; /&gt;  
      &lt;attribute name=&quot;sms_emailid&quot; /&gt;  
      &lt;attribute name=&quot;sms_country&quot; /&gt;  
      &lt;attribute name=&quot;sms_age&quot; /&gt;  
      &lt;order attribute=&quot;sms_firstname&quot; descending=&quot;false&quot; /&gt;  
      &lt;/entity&gt;  
      &lt;/fetch&gt;  
     </b:Query>  
    </query>  
   </RetrieveMultiple  
 </xsl:template>  


                                               Fig – XSLT Used in Scripting




Fig – End to End Output FetchExpression Request, Response with Count Set to 2





·         Using Page Cookie using QueryExpression



Fig – Mapping to QueryExpression Request, Response with Count Set to 2


 <xsl:template name="QueryExpression">  
   <xsl:param name="Count" />  
   <xsl:param name="pageIndex"/>  
   <xsl:param name="pageCookie" />  
   <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">  
    <query i:type="b:QueryExpression" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  
     <b:ColumnSet>  
      <b:AllColumns>false</b:AllColumns>  
      <b:Columns xmlns:e="http://schemas.microsoft.com/2003/10/Serialization/Arrays">  
       <e:string>sms_registrationpageid</e:string>  
       <e:string>sms_firstname</e:string>  
       <e:string>sms_mobile</e:string>  
       <e:string>sms_lastname</e:string>  
       <e:string>sms_ismarried</e:string>  
       <e:string>sms_gender</e:string>  
       <e:string>sms_emailid</e:string>  
       <e:string>sms_country</e:string>  
       <e:string>sms_age</e:string>  
      </b:Columns>  
     </b:ColumnSet>  
     <b:Criteria>  
      <b:Conditions/>  
      <b:FilterOperator>And</b:FilterOperator>  
      <b:Filters/>  
     </b:Criteria>  
     <b:Distinct>false</b:Distinct>  
     <b:EntityName>sms_registrationpage</b:EntityName>  
     <b:LinkEntities/>  
     <b:Orders>  
      <b:OrderExpression>  
       <b:AttributeName>sms_firstname</b:AttributeName>  
       <b:OrderType>Ascending</b:OrderType>  
      </b:OrderExpression>  
     </b:Orders>  
     <b:PageInfo>  
      <xsl:choose>  
       <xsl:when test="$Count">  
        <b:Count>  
         <xsl:value-of select="$Count"/>  
        </b:Count>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:Count>0</b:Count>  
       </xsl:otherwise>  
      </xsl:choose>  
      <xsl:choose>  
       <xsl:when test="$pageIndex">  
        <b:PageNumber>  
         <xsl:value-of select="$pageIndex"/>  
        </b:PageNumber>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:PageNumber>0</b:PageNumber>  
       </xsl:otherwise>  
      </xsl:choose>  
      <xsl:choose>  
       <xsl:when test="$pageCookie">  
        <b:PagingCookie>  
         <xsl:value-of select="$pageCookie"/>  
        </b:PagingCookie>  
       </xsl:when>  
       <xsl:otherwise>  
        <b:PagingCookie i:nil="true"/>  
       </xsl:otherwise>  
      </xsl:choose>  
      <b:ReturnTotalRecordCount>false</b:ReturnTotalRecordCount>  
     </b:PageInfo>  
     <b:NoLock>false</b:NoLock>  
    </query>  
   </RetrieveMultiple>  
  </xsl:template>  

                                              Fig – XSLT Used in Scripting



Fig – End to End Output QueryExpression Request, Response with Count Set to 2


            


MSDN Sample Using C#


Sample: Use QueryExpression with a paging cookie: https://msdn.microsoft.com/en-in/library/mt269606.aspx 


Sample: Use FetchXML with a paging cookie: https://msdn.microsoft.com/en-us/library/gg328046.aspx 

Building the Sample


Follow the below-detailed steps to Run the below sample.
  •  It has some pre requisites if you are checking this article for the 1st time, Refer the below see also sections articles for related BizTalk artifacts for more details to run this sample End to End.
  • To download the sample BizTalk Application for using Dynamics CRM Online / On Premise CRM Paging Cookie, Refer here MSDN Code Samples  
  •  Zip File Includes: BizTalk Sample Solution, Application Port Bindings, Sample Retrieve Multiple Request, Response files for FetchExpression, QueryExpression.


 


Conclusion


·         In this article you have learn on how to utilize the CRM Online / On premise Paging, Paging Cookie features.
·         Using this technique , you can minimize the load on BizTalk, when you are working with bulk data processing as Retrieve Response.
·         You could choose which query expression you wish to go with depending on the scenarios, both are similar the way we write expression is difference.

     See Also



      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


Monday, 5 September 2016

Comparison of Software Requirement of different versions of BizTalk Servers



This page lists the software required for running BizTalk Server. 
         
Software
BizTalk Server 2009
BizTalk Server 2010
BizTalk Server 2013
BizTalk Server 2013R2
BizTalk Server 2016
Microsoft Windows
Windows Server 2008, Windows Server 2003, Windows® XP with Service Pack 3 (SP3), Windows Vista® with Service Pack 1 (SP1).
Windows 7, Windows Server 2008 R2, Windows Server 2008 R2 SP1, Windows Server 2008 SP2, Windows Vista® with Service Pack 2 (SP2).
Windows Server 2012, Windows Server 2008 R2 SP1, Windows 7 SP1, Windows 8

Windows Server 2012 R2, Windows Server 2012, Windows 8.1, Windows 7 SP1.
Windows Server 2016 PP1/TP4, Windows Server 2012 R2,, Windows 10, Windows 8.1
Internet Information Services (IIS)
Versions 6.0 and 7.0 are supported. Provides a scalable Web application infrastructure.
Required for: EDI, BAM, WSS Adapter, and UDDI.
Version 7.5 and 7.0 is supported. Provides a scalable Web application infrastructure.
Required for: EDI, BAM, WSS Adapter, and UDDI.
The version that comes with the operating system. KB 224609 lists the versions.
The version that comes with the operating system. KB 224609 lists the versions.
The version that comes with the operating system.
Windows Identity Foundation


Required when using the Windows SharePoint Services Client Side Object Model (CSOM).
Appendix B: Install the Microsoft SharePoint Adapter lists the specific BizTalk Server Windows SharePoint Services adapter options.

Required when using the Windows SharePoint Services Client Side Object Model (CSOM).
Appendix B: Install the Microsoft SharePoint Adapter lists the specific BizTalk Server Windows SharePoint Services adapter options.

Required when using the Windows SharePoint Services Client Side Object Model (CSOM).
Microsoft SharePoint
WSS 2.0 with Service Pack 3 (SP3), WSS 3.0 with Service Pack 1 (SP1), and Microsoft Office SharePoint Server 2007 (MOSS).
SharePoint Services 2010, SharePoint Foundation 2010, WSS 3.0 with Service Pack 2 (SP2), and Microsoft Office SharePoint Server 2007 Service Pack 1 (SP1) (MOSS).

SharePoint Services 2013, SharePoint Services Online, SharePoint Services 2010, SharePoint Services 2007
SharePoint Services 2013 SP1, SharePoint Services Online, SharePoint Services 2010
SharePoint 2016,
SharePoint 2013 SP1,
SharePoint Online
Microsoft Office
Microsoft Office Excel 2007 
Microsoft Office Excel 2010 or 2007
If you are using Microsoft Office Excel 2010, you must install Excel 2010 hotfix package – Knowledge Base article 2345338 (Description of the Excel 2010 hotfix package (excelc-x-none.msp, excel-x-none.msp): August 31, 2010).

Microsoft Office Excel 2013 or 2010.
If using Excel 2010, install KB 2345338.

Microsoft Office Excel 2013 or 2010
If using Excel 2010, install KB 2345338.

Microsoft Office Excel 2016 or 2013
Microsoft .NET Framework
Microsoft .NET Framework 3.5 with Service Pack 1 (SP1)
Microsoft .NET Framework 4 and .Net Framework 3.5 with Service Pack 1 (SP1) 

.NET Framework 4.5.x
.NET Framework 4.5.x
.NET Framework 4.6
Microsoft Visual Studio
Microsoft Visual Studio 2008 
Microsoft Visual Studio 2010 
Microsoft Visual Studio 2012
Microsoft Visual Studio 2013
Microsoft Visual Studio 2015
Microsoft Visual C++ Redistributable Package


Microsoft Visual C++ 2010 Redistributable Package.
On an x86 computer, install only the x86 version of the package. On an x64 computer, install both x86 and x64 versions of the package. The installer is available in the BizTalk Server installation under \Platform\SSO\Platform
Microsoft Visual C++ 2010 Redistributable Package.
On an x86 computer, install only the x86 version of the package. On an x64 computer, install both x86 and x64 versions of the package. The installer is available in the BizTalk Server installation under \Platform\SSO\Platform
Microsoft Visual C++ 2013 Redistributable Package.
Only the x86 version is required to be installed. Please install from
Microsoft SQL Server
SQL Server 2005 SP2 or 2008 
SQL Server 2008 R2 SP1 or SQL Server 2008 R2/2008 SP1 
SQL Server 2012 or SQL Server 2008 R2 SP1
For optimal performance, Microsoft recommends the Enterprise Edition of SQL Server. To fully use the BizTalk Server SDK or deploy BizTalk Server applications from a Visual Studio, install the SQL Server Development Tools.

SQL Server 2014 or SQL Server 2012 SP1
For optimal performance, Microsoft recommends the Enterprise Edition of SQL Server. To fully use the BizTalk Server SDK or deploy BizTalk Server applications from a Visual Studio, install the SQL Server Development Tools.

SQL Server 2016 or SQL Server 2014 SP1
For optimal performance, Microsoft recommends the Enterprise Edition of SQL Server. To fully use the BizTalk Server SDK or deploy BizTalk Server applications from a Visual Studio, install the SQL Server Development Tools.

SQL Server Database Mail / SQL Server 2005 Notification Services
SQL Server 2005 Notification Services   Note that this SQL Server 2005 feature is not included in SQL Server 2008, and must be downloaded separately.

SQL Server 2005 Notification Services   
SQL Server 2005 feature is not included in SQL Server 2008 R2 SP1/SQL Server 2008 R2/2008 SP1, and must be downloaded separately.

If you’re using SQL Server 2008 R2, you must install SQL Server 2005 Notification Services. Notification Services is not included with SQL Server 2008 R2. Download SQL Server 2005 Notification Services from Feature Pack for Microsoft SQL Server 2005 - December 2008.
If you’re using SQL Server 2012, configure SQL Server Database Mail

Configure 
SQLXML 4.0 with Service Pack 1
SQLXML 4.0 with Service Pack 1 
SQLXML 4.0 with Service Pack 1 
To install SQLXML 4.0 SP1, see  Microsoft SQL Server 2008 Feature Pack

To install SQLXML 4.0 SP1, see SqlXml 4.0 Service Pack 1 (SP1).
To install SQLXML 4.0 SP1,