BizTalk Server & Dynamics CRM Online: Integration with On-premises Lines of Business Systems Using BizTalk Server 2013

This is the eighth blog post in this series.  You can find the previous posts here:

• Innovations in Integration on the Microsoft Platform
• Lowering Barriers to Innovation: BizTalk IaaS and Walkthrough
• BizTalk Sever 2013 Enhancements
• Windows Azure BizTalk Services EDI Overview Including Portal Configuration
• Windows Azure BizTalk Services – Utilizing EAI Bridge
• Windows Azure BizTalk Services: Integrating with on-premises LOB systems usingthe BizTalk Adapter Services
• ReST (Representational State Transfer) in BizTalk 2013

Companies that leverage Dynamics CRM Online want to take full advantage of its rich set of capabilities by integrating with existing lines of business systems that are available on premise. It is common nowadays for organizations to have other lines of business systems that are hosted on premise, behind the firewall.

In this initial blog, I will show one solution that is available for integrating CRM Online with BizTalk 2013 hosted on premise. In the first scenario, I will demonstrate how data can be sent from the BizTalk application to CRM Online.

BizTalk to CRM Online Integration

Fig. 1: Overall Architecture of main components

CRM Online exposes the Organization Service (IOrganizationService). This is a built-in CRM WCF service that is used to interact with CRM data and metadata.

I will be simulating creating a new account via BizTalk and show how to send it to CRM Online. In order to run this scenario, you will need the following:

• CRM Online account
• CRM SDK
• BizTalk Server 2013

Summary of steps (additional details and screenshots below):

1. Navigate to the CRM online organization instance to retrieve the OrganziationService URL.
2. Use BizTalk WCF Service Consuming wizard to generate schema from the Organization Service web service.
3. Create source schema (Account schema).
4. Map source schema to the CRM online schema generated in step 2.
5. Create orchestration (CreateAccount orchestration).
6. Create custom binding and behavior; register them in BizTalk.
7. Configure WCF custom send port.
8. Configure BizTalk.
9. Simulate sending message by dropping the account sample file in the predefined folder.
10. Log into CRM Online and verify that the account has been created.

Using Organization Service to interact with CRM Online Walkthrough:

Step 1 – Log into CRM Online to retrieve Organization Service URL;

Go to Settings – > Customizations -> Developer Resources

Step 2 – Use BizTalk Publish Wizard to generate the schema and the binding file.

In BizTalk solution right-click schema project, select Add and then Add Generated Items; selectConsume WCF Service Wizard; navigate through the wizard and enter the URL of your Dynamics Organization Service endpoint ( the url discovered in Step1); after clicking the Get button, click ‘Next’ and select Import.

Step 3 – Replace the generated Organization Service schema with the BizTalk schemas that are provided via CRM SDK (\SDK\Schemas\CRMBizTalkIntegration)

Below is the organizationservice_schemas_microsoft_com_xrm_2011_contracts_services schema. Note that the schema provides CRUD operations in addition to the other CRM-related operations. The nodes that are being used to create a new account are highlighted below. The schema provided by CRM is flexible; it exposes a key-value pair; the key maps to the CRM entity attribute (i.e. name, address1, city, or phone). LogicalName maps to the CRM entity name (i.e. account).

Step 4 – Create account schema.

Step 5 – Map account schema to the expected CRM schema format.

Step 6 – Create orchestration in BizTalk. The orchestration is receiving the message viarcvAccountPort; it then uses Construct Message shape to map the message received to the expected Organization Service schema and sends it to CRM. The response message is captured and written to the file system.

Step 7 – Deploy BizTalk artifacts to BizTalk.

Step 8 – Configure OrganizationService send port.

Import the binding configuration (OrganizationService_Custom.BindingInfo) generated in Step-2. Rename the port from auto generatedWcfSendPort_OrganizationService_CustomBinding_IOrganizationService_Custom toOrganizationService.

CRM Online is using Windows Live Id authentication. In order to configure the BizTalk send port to authenticate with CRM Online instance, a custom WCF behavior has to be built and registered in the Global Assembly Cache (GAC) and the configuration file (machine.config or in BizTalk WCF-Custom send handler).

Mikael Håkansson’s blog post provides a detailed explanation with the sample code for creating the custom WCF behavior.  I leveraged it for this demo. See below the detailed steps necessary to configure the OrganizationService send port.

OrganizationService send port.

OrganizationService Send Port – General tab view.

Note that no changes have been made to the custom binding generated in Step 2.

OrganizationService Send Port – Binding tab view.

Select customBinding as the Binding Type.

OganizationService Send Port – Behavior tab view

I am using liveIdAuthentication custom behavior which requires the following three properties:

• crmuri –  the organization service url   (https://yourorgname.api.crmapi.crm.dynamics.com/XRMServices/2011/Organization.svc)
• username – CRM username
• password – CRM password

Step 9 – Start BizTalk application.

Drop the sample file that contains account-related information in the predefined incoming folder.

Sample file:

Verify in CRM Online that the account has been added.

Verify the response is received in the expected file system folder.

The response contains the account id which was automatically generated when the new account entity was created in CRM.

Response Message:

The scenario that was presented above used the WCF SOAP Organization Service to integrate with the CRM Online application. Another option typically used for interacting with CRM Online is for BizTalk to leverage a proxy service which in turn uses CRM SDK API to authenticate and communicate with CRM; crmsvcutil tool is part of CRM SDK and can be leveraged to create early bound entity CRM classes that can be used in the proxy service. The proxy service approach is recommended for more complex integration scenarios.

Useful Resources:

• CRM Developer Center
• CRM Online 30 day trial subscription
• Creating WCF Custom Behavior to Connect to CRM Online by Mikael Håkansson
• CRM Online 2011 WebService – SOAP Only Client by Girish Raja
• Use the IOrganizationService Web Service to Read and Write Data and Metadata
• Integrating BizTalk Server and Microsoft Dynamics CRM by Richard Seroter
• CRM 2011 Integration How to Video #1: BizTalk On-Premise to CRM 2011 by Peter Kelcey

Integrating CRM 2011 using SQL Integration Services 2012

Import of data to CRM

Datasource

As a data source I will use table of Microsoft SQL Server 2012 (but it could be whatever you want – Excel, CSV, Oracle DB e.t.c.). Here is the script to create source table:

CREATE TABLE [dbo].[Contact](
    [FirstName] [varchar](100) NOT NULL,
    [LastName] [varchar](100) NOT NULL,
    [Phone] [varchar](100) NOT NULL,
    [Email] [varchar](100) NOT NULL
) ON [PRIMARY]

SSIS Package

Create new SSIS Project:

Add new DataFlow Task to package:

Open DataFlow task and add new OLE DB Source:

Configure Connection for Source:

Recheck that it is possible to retrieve data from datasource with preview button:

Add Script Component to your dataflow and link DataSource and newly added Script Component:

Open Script Component and configure Input Columns:

Open Script tab and click Edit Script button:

New Instance of Visual Studio 2012 will be opened and you should see something similar to:

Add references to CRM SDK assemblies, System.Runtime.Serialization and System.ServiceModel:

References list should look like:

Modify main.cs to following:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using System.ServiceModel.Description;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IOrganizationService organizationservice;

    public override void PreExecute()
    {
        base.PreExecute();

        ClientCredentials credentials = new ClientCredentials();
        credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
        organizationservice = new OrganizationServiceProxy(
            new Uri("http://crm/contoso/XRMServices/2011/Organization.svc"), null, credentials, null);
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Entity newcontact = new Entity("contact");

        if (!Row.FirstName_IsNull)
            newcontact["firstname"] = Row.FirstName;

        if (!Row.LastName_IsNull)
            newcontact["lastname"] = Row.LastName;

        if (!Row.Email_IsNull)
            newcontact["emailaddress1"] = Row.Email;

        if (!Row.Phone_IsNull)
            newcontact["telephone1"] = Row.Phone;

        organizationservice.Create(newcontact);
    }

}

Save and close VS, save Script Component and run package:

Results in CRM:

Usage of CRM as a datasource

In my experience I had situations when CRM Data was used as a datasource. Following part of article describes how to use CRM Data as a datasourse. Of course for OnPremice it is possible to use SQL to access data but the same approach would not work for Partnet hosted CRM or CRM Online.

Create new package, new DataFlow Task and add new Script Component with Source Type:

Open Script Component Properties, Inputs and Outputs tab, modify outputs:

Open Script Tab and click Edit Script button:

In new Instance of Visual Studio add references to CRM SDK assemblies, System.Runtime.Serialization and System.ServiceModel, modify CreateNewOutputRows method:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Xrm.Sdk;
using System.ServiceModel.Description;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    IOrganizationService organizationservice;

    public override void PreExecute()
    {
        base.PreExecute();

        ClientCredentials credentials = new ClientCredentials();
        credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

        organizationservice = new OrganizationServiceProxy(
            new Uri("http://crm/contoso/XrmServices/2011/Organization.svc"), null, credentials, null);
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void CreateNewOutputRows()
    {
        QueryExpression query = new QueryExpression("contact")
        {
            ColumnSet = new ColumnSet(new string[] { "fullname", "createdon", "emailaddress1" }),
            PageInfo = new PagingInfo()
            {
                Count = 250,
                PageNumber = 1,
                ReturnTotalRecordCount = false
            }
        };

        EntityCollection results = null;

        do
        {
            results = organizationservice.RetrieveMultiple(query);

            foreach (Entity record in results.Entities)
            {
                ContactBuffer.AddRow();

                ContactBuffer.ContactId = record.Id;
                ContactBuffer.CreatedOn = record.GetAttributeValue<DateTime>("createdon");

                if (record.Contains("fullname"))
                    ContactBuffer.FullName = record.GetAttributeValue<string>("fullname");

                if (record.Contains("emailaddress1"))
                    ContactBuffer.Email = record.GetAttributeValue<string>("emailaddress1");
            }
        }
        while (results.MoreRecords);
    }

}

Save and close Visual Studio, add destination to your DataFlow (in my case I used text file but you can use any type of datadestination you want):

Run package and check results: