Using Microsoft BizTalk Dynamics CRM Adapter – Part 1


Recently, I have been working on an integration project where few exiting systems were getting integrated with Microsoft CRM System 3.0. We architected to use BizTalk CRM adapter to synchronize and integrate data with CRM system. We could not locate a good documentation about adapter usage. We had to do lots of research, hit-&-try POCs to understand CRM adapter usage. And this encouraged me to come up with set of articles which can facilities developers to understand and use adapter easy and fast.
I am thinking to cover some basic operations with BizTalk CRM adapter such Query Data, Create Data, Update Data, Delete Data, Execute Operations, Retrieve Data etc. I will also cover dealing with complex data types (Pick List, Lookup) and custom entities/attributes.


I am expecting reader to have basic understanding of schemas, maps, orchestration, ports and adapters.


How to Install BizTalk CRM Adapter


Adapter installation is simple. You can download installation bits and usage guide from http://www.microsoft.com/downloads/details.aspx?FamilyID=4628fca6-388d-45bc-a154-453b920dbcb8&displaylang=en.


Following are key points of installation and usage guide –


1.    Installation is simple and setup based.


 


2.    CRM system uses AD based security schema. BizTalk uses send or solicit-response ports with CRM adapter to talk to CRM system. BizTalk ports run under host. If your BizTalk host service account does not have adequate access on CRM system then you are required to create another host with account which has sufficient access permission on CRM system. And then you construct a new CRM send handler which runs under new host and then configure CRM related ports to run new CRM send handler.


 


3.    Installation guide also talks about creating port and generating schema which I will also cover in coming sections.


 


4.    I recommend readers to install CRM SDK which holds good quantity of code samples and information. You can find SDK @ http://www.microsoft.com/downloads/details.aspx?familyid=9C178B68-3A06-4898-BC83-BD14B74308C5&displaylang=en.


 


5.    It is useful to take look at overall installation and usage document. Especially known issues section.


 


Basics of CRM System


Before we chat about using CRM adapter, we will cover some nuts and bolts which are essential for any BizTalk developer to work with CRM. CRM application can be access using following URL (though it depends on configuration of CRM site) - http://<CRM-Sever>/loader.aspx.


You will find that CRM system is divided into multiple sub-systems such Sales, Marketing, Service etc. With extension of CRM GP, accounting sub-systems also come in picture. Each of these sub-systems maintains a range of information like “Service” sub-system maintains Cases (customer escalations), Accounts, Contacts, Products etc.


In technical vocabulary, information is hold by various entities like we have one entity each for case, account, contact etc. These entities generally hold association/relationship among themselves. For example one account can have multiple contacts, one contact can have more than one case, there is many-to-many relationship between accounts and products etc.


Each of these entities follows a schema. Schema defines attributes hold by these entities and their type information. For example contact has Last Name (string), First Name (string), Gender (Pick List) etc as attributes. If you require then you can also create new custom entities or new custom attributes. When working with CRM system, we generally deal with these entities and their attributes. We create, update or delete instances of these entities.


Before we start integration with CRM system we need to understand entities, their schema, relationship with other entities etc. You can go to “Settings >> Customization >> Customize Entities” to see list of entities available in CRM system. List shows name, schema name, type and description. Here you will find option to create new custom entity or modify existing entity.


If you open (double-click) any existing entity, it will show you details of it. You will see details of attributes, forms/views to populate these entities in CRM system, relationship with other entities etc. When you check “Attributes” of entity, you will see attribute schema name, display name, type and description. Attribute schema name and type is very important information because BizTalk CRM adapter generates schema based on schema name and type information inside CRM system. You will also find provision to create new custom attributes.


CRM sub systems, forms, entities, schemas and customization are self explanatory. I suggest readers to spend some time with it; play around and get comfort. 


It is time to get into coding now.


How to perform Query Data with CRM Adapter


Dynamics CRM provides a web service which can be used by developers to do operations against CRM system. CRM Adapter internally uses this web service for operations. Generally, you will find web service URL as –


http://<CRM-Server>/MSCRMServices/2006/CrmService.asmx.


Without documentation, it was hard for me to perform POCs with BizTalk CRM adapter. To make my job simpler, I played around with Dynamics CRM web service to manipulate entities. And this gave me adequate idea when dealing with generated CRM schemas in BizTalk. I am planning to follow same trick in these articles also - .Net code first and then BizTalk implementation.


We are going to see how query can be performed against CRM system. I am going to query case details using case guid. Case is complaints or service requests registered by customers and it comes under “Service” sub-system.


1.    Perform Query using .net code (calling CRM web service)


I added CRM web service as web reference and named it “CRMServer”. Following is code to query case using case guid.


 CRMServer.CrmService service = new CRMWSTest.CRMServer.CrmService();


 service.Credentials = System.Net.CredentialCache.DefaultCredentials;


 string fetchXml = @"<fetch mapping=""logical"">


                                  <entity name=""incident"">


                                     <attribute name=""ticketnumber""/>


                                     <attribute name=""title""/>


                                     <filter type=""and"">


                                          <condition attribute=""incidentid"" operator=""eq"" value=""7c89ec34-6828-410b-9718-185b5a39a8ba""/>


                                      </filter>


                                    </entity>


                                 </fetch>";


 string result = service.Fetch(fetchXml);


Code is easy to understand. I first created an instance of CRM web service client and then create query (fetch) XML. Fetch xml is straightforward to understand.


  <entity name=""incident""> 


Means entity which I wish to query is “incident”. Well, case is the display name and schema name for case is “incident”. You can find this in customization section as mentioned in previous section.


<attribute name=""ticketnumber""/>


 <attribute name=""title""/>


I am querying “Case Number” and “Title” attributes of case. Schema name of these two attributes are “ticketnumber” and “title”. You can add more attributes as required.


 <filter type=""and"">


       <condition attribute=""incidentid"" operator=""eq"" value=""7c89ec34-6828-410b-9718-185b5a39a8ba""/>


</filter>


This is filter condition to query. Type is “and” as logical operator. It can also accept “or”. “attribute” is entity attribute which we are using for query. Again this is schema name not display name. “operator” is assignment operator and it can accept values as “lt” (less than), “gt” (greater than), “le” (less than or equal), “ge” (greater than or equal), “eq” (equal), “ne” (not equal), etc. Finally “value” holds value of attribute to be queried. If required, you can use more attributes with “and/or” options. I suggest readers to refer CRM SDK for more details about fetch xml.


Finally we call “Fetch” method passing “fetchXml” as parameter.


Query result comes in following format.


<resultset morerecords="0" paging-cookie="$1$$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$!$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$">


  <result>


    <ticketnumber>CAS-01006-ZBEJ0W</ticketnumber>


    <title>This is test case1</title>


  </result>


</resultset>


Result is self illustrative. You can see ticket number and title returned for query.


 


2.    Perform Query BizTalk code (using Dynamics CRM Adapter)


Above was query implementation in .net code. Now we are going to implement same logic in BizTalk orchestration. Following are the steps to perform same query operation in orchestration using BizTalk Dynamics CRM adapter.


·         In query we send Fetch XML and return result. We need solicit-response send port for this purpose. Create a new static solicit-response send port in BizTalk Administration Console. Give a name to it (say “CRMSendPort”) and select type as “Microsoft Dynamics CRM”. Go to “Configure” and put “Web Service URL” as http://<CRM-Server>/mscrmservices/2006.


In send handler, select send handler created as per CRM specific host in above section. Configure “Send Pipeline” as “Xml Transmit” and “Receive Pipeline” as “Xml Receive”. Save and close things.


 


Note: One of the common mistakes committed by developers is to put complete web service URL (http://<CRM-Server>/MSCRMServices/2006/CrmService.asmx) in configuration. This generates error during schema generation. Reason is that CRM adapter uses “Metadata.asmx” to generate schema and it just need URL of CRM web service virtual directory.


 


·         Create a BizTalk project and assign a strong name key to it.


 


·         We have to generate schema for query operation. Right click Project Name >> Select Add Menu >> Select Add Generated Items Menu>> Select Add Adapter Metadata Option>> Press Add Button >> Select Microsoft Dynamics CRM Option>> Select Port (same port created in above steps say “CRMSendPort”) >> Press Next Button. “Microsoft Dynamics CRM User Credentials” screen opens up. Enter user name and password for CRM system. Remember this user name and password should have adequate access to fetch entity and their schema information from CRM system. Press Ok button after entering credentials.


 


·         “Microsoft Dynamics CRM Actions and Entities” screen opens up. As we already discussed, CRM sub-systems are made of entities. At this point we select entity (account, contact, case etc.) which we want to deal with and action (add, update, delete, execute etc.) which we want to perform on selected entity. Since we want to perform query on cases (incidents), select “ExecuteFetch” as action and “Incident” as entity. If required, in some implementation, you can also select multiple actions and multiple entities. When selection is done, press next button. It would take few seconds to generate all schemas related to fetch operation in BizTalk project.


 


·         In generated artifacts, it generates lots of schema and one BizTalk orchestration file. Open orchestration file and delete all port types and multi-part message types generated by default. Well, these are useful but I am asking to clean them so that we can create and understand things from scratch.


 


·         It generates some 10 schemas in project. These schemas contain type definition and are included in each other. We are interested in two schemas only – one request schema which can send our query/fetch XML and one response schema which can pull result back.


 


·         “ExecuteFetch_ExecuteFetchRequest.xsd” serves purpose of request schema. You expand to see details of this schema. It has two important elements – “crm_action” and “FetchXml”. We will check these in details in few seconds.


 


·         Now response is tricky part. Developers think that “ExecuteFetch_ExecuteFetchResponse.xsd” is response schema but it is NOT. Every response using CRM adapter follows a fix and common schema and this schema is not generated. You can find this schema @ “C:\Program Files\BizTalkAdapter\Schemas\Response.xsd” depending upon adapter installation location. You have to include this Response.xsd in project.


 


·         We are now all set to implement logic. Create two messages of type request and response schemas. Hook these messages to a static solicit-response send logical port using send and receive shape. Use map (with transform shape) or message assignment shapes to create request xml and send it via solicit-response port. When response comes consume as per requirement. I am leaving this logic development part to readers. If you face any issue, please refer sample application attached with this article or feel free to message me.


 


·          When creating xml request (using map or message assignment), take care of following things. Set “crm_action” attribute value to “execute”. We are doing this because we are executing fetch operation on CRM system. Other possible values are “create”, “update” and “delete”. We will see use of this in coming articles. Set “FetchXml” element value to - "<fetch mapping='logical'><entity name='incident'><attribute name='ticketnumber'/><attribute name='title'/><filter type='and'><condition attribute='incidentid' operator='eq' value='7c89ec34-6828-410b-9718-185b5a39a8ba'/></filter></entity></fetch>"


Remember this is same format which we used in previous .net code sample. In attached sample, I used script functoids in map to prepare request XML. I have implemented in crude way to keep things simple but you can do better and smart job.


 


·         When you are done with coding, compile project and deploy it. After deployment, bind solicit-response orchestration ports with physical solicit-response port (“CRMSendPort”) created in above steps. Enlist and start orchestration. Finally trigger orchestration to test query operation.


 


·         You will find that response message comes in following format –


 


<ns0:Response xmlns:ns0="http://schemas.microsoft.com/crm/BizTalkAdapter/Response">


  <Header>


  <ReturnCode>1</ReturnCode>


  <ErrorCode />


  <ErrorString />


  <Retryable />


  </Header>


<Body>


  <Message><prefix:ExecuteFetchResponse xmlns:prefix="http://localhost/schemas.microsoft.com/crm/2006/ExecuteFetchResponse"><FetchXmlResult>&lt;resultset morerecords='0' paging-cookie='$1$$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$!$incident$incidentid$1$0$38${7C89EC34-6828-410B-9718-185B5A39A8BA}$'&gt;&lt;result&gt;&lt;ticketnumber&gt;CAS-01006-ZBEJ0W&lt;/ticketnumber&gt;&lt;title&gt;This is test case1&lt;/title&gt;&lt;/result&gt;&lt;/resultset&gt;</FetchXmlResult></prefix:ExecuteFetchResponse></Message>


</Body>


</ns0:Response>


“Message” element contains response (xml format) and it follows schema type ExecuteFetch_ExecuteFetchResponse.xsd. You need to employ some parsing technique to fetch information out of response xml.


Rest of the return message contains error handling mechanism which can be used as required.


·         That’s it. Make tweaks in request message and orchestration logic to explore query/fetch option thoroughly.


Conclusion


This was a high level walk through and a sample to query CRM system for entities. If required, please download sample project attached with article for further reference. If you face any issue please feel free to ping me on this article. Hope article was useful to you and your comments are always welcome.
Please wait for some time before we meet again with next part of it.
 


Next Part URL - http://blogs.msdn.com/brajens/archive/2007/05/30/using-microsoft-biztalk-dynamics-crm-adapter-part-2.aspx


 

QueryIncidentOrch.zip


Comments (18)

  1. Great Post on the BizTalk Adapter for CRM…I am doing a session on this @ TechEd, and this article rocks…

  2. alebas says:

    The article is great. Easy to understand and helpful. Far better that any docs. Thank you.

  3. BTS Bits says:

    In previous first part of the article, we had initial talks about integration between BizTalk and CRM

  4. sapthagiri says:

    iam waiting for update,delete.

    and also how to handle picklist and lookups in biztalk mapping and orchestration.

    Please post as early as possible.At present we are using this consept in our project.

  5. brajens says:

    Hi sapthagiri

    I might take some time to write next part. Meanwhile, send your email id to singhbraj@googlemail.com.

    I will try to send you a writeup with sample in 1-2 days.

  6. sapthagiri says:

    your email is not working my email id is :k.sapthagiri@gmail.com .

    please give me an example for handling the Picklist and Lookups in mapping and orchestraion also.

    Waiting for ur reply

    will you give  your email id so that i will send our orchestration flow chart also.

    Waiting for the reply

  7. OMG! I just found this awesome post on integration of Dynamics CRM with BizTalk. Brajendra Singh does

  8. BTS Bits says:

    In previous articles, we talked about – · Installing and configuring BizTalk CRM Adapter · Basics of

  9. Betolea says:

    So, basically we need to create a file with the code we want to execute. How do we embed the code/file into our CRM?

  10. brajens says:

    Hi Betolea

    I could not get your point.

    if you are writting .net code against CRM then you simply need to reference CRM web service and use its APIS.

    However, if you are writting BizTalk against CRM then you can use CRM adapter as specified in article.

    Please send me you doubt more elaborated so that I can help.

  11. srikanthrapolu says:

    Your application has helped me a lot in doing a POC

  12. srikanthrapolu says:

    I am facing the following error after it is picking up the file from file drop folder:

    The adapter failed to transmit message going to send port "CRMReqRespPort" with URL "http://10.136.145.191/mscrmservices/2006/&quot;. It will be retransmitted after the retry interval specified for this Send Port. Details:"The request failed with HTTP status 401: Unauthorized.".

  13. Paritosh says:

    A great post… was exploring the features of the crm adapter from implementation pt of view and this post beats every documentation available.

  14. impossible says:

    i try to connect to crm but i keep getting an error:

    ‘The list of actions and entities canot be loaded: The request failed with HTTP status 401: Unauthorized. : Check you user name and password, and varify that you have the correct permissions to access this service.

    how can i confirm the username and password are correct?

    when i use a web browser i can get to the web services. using the same username and password.

  15. Marie says:

    Great walkthrough you have on how to use crm adapters. Being a newbie on Biztalk I need some help to solve a problem I’m working on currently. I have worked quite a lot with CRM, so nothing new for me there. What I’d like to do with the crm adapter in Biztalk is to get all records for let’s say account. In my transform I have executefetchtrequest as my destination, now to my problem…what to have as the source. I tried different things but in vain, I’m absolutely clueless I hope you have time to help me on this.

    Many thanks,

    Marie

  16. kuldip.potadar@proteans.com says:

    HI,

    i want to fetch data from CRM.4.0

    For this i am using FetchXmlToQueryExpression crm action.

    I am using FetchXmlToQueryExpression_FetchXmlToQueryExpressionRequest.xsd to send fetchXML request by setting attribute as:

    crm_action : "execute"

    FetchXML: FetchXML query as an output of Scripting Functoid.

    And using response.xsd coming with BTS 2006 adpater for MS CRM 4.0 to receive response from MS CRM

    i set the target namespace for reponse.xsd as: targetNamespace="http://schemas.microsoft.com/crm/2007/BizTalkAdapter/MicrosoftCRM/Response&quot; according to the organization name here orgName: "MicrosoftCRM"

    Now i am getting an error:

    Exception thrown from: segment -1, progress -1

    Inner exception: Received unexpected message type ” does not match expected type ‘http://schemas.microsoft.com/crm/2007/BizTalkAdapter/MicrosoftCRM/Response#Response

    Exception type: UnexpectedMessageTypeException

    Source: Microsoft.XLANGs.Engine

    Target Site: Void VerifyMessage(Microsoft.XLANGs.Core.Envelope, System.String, Microsoft.XLANGs.Core.Context, Microsoft.XLANGs.Core.OperationInfo)

    Thanks,

  17. Here are a great series of articles on integrating BizTalk Server with Dynamics CRM: 1. Using Microsoft

  18. very useful information.

    Thanks,

    Israel

Skip to main content