IIB ESB Integration of MySQL table, MailChimp, MS Dynamics CRM using HTTP Request Node, MQ and JSON API

This is the second part of whole article, read the first part here.

Introduction

At the time when I write these lines, the world’s digital infrastructure is characterized by a plethora of data interchange formats.

Twenty years ago, XML was the primary data interchange format. When it came on the scene, it was a breath of fresh air and a vast improvement over the cumbersome SGML. It enabled people to do previously unthinkable things, like use functional languages for something useful (sarcasm). With all the dissatisfaction surrounding XML, it’s easy to forget just how important it used to be in the evolution of the web in its capacity as a “Leatherman multitool of the internet.”

But it’s no secret that in the last few years, a bold transformation has been visible in the world of data interchange. The more lightweight, bandwidth-efficient JSON (JavaScript Object Notation) has emerged .

Believe it or not, today’s web landscape remains heavily siloed for a variety of reasons and from different points of view. There’s amazing amounts of information out there that are secret. But beginning with companies like eBay in the mid-noughties, APIs have come along as a kind of siloe breaking force.

This has created a scenario in which organizations like Twitter, Facebook, LinkedIn, and sound number of others first, essentially offer information-based services in exchange for data, and second, increasingly have an interest in opening up a wide variety of information to third parties. The changes in that space have a marking visibility on the rest of the web.

There isn’t enough of hard data on XML vs. JSON usage in APIs, but most of the opinions leaders strongly suggest that XML is still a major player in the world of APIs, but that JSON’s star is rising fast. Twitter’s API went JSON-only before 2012. Foursquare has followed suit.

At present when it comes to the data APIs, XML is still the most used format, but JSON is growing much faster. Even though there are still more XML APIs, the more recent the API, the more likely it’s serving JSON. Often providers are rapidly moving from XML to JSON.

This is true as well for our bulk mail service provider MailChimp. Their current version 2.0 of API became available a couple of months ago, it is recommended and it’s full JSON.

Scenario

Syncing from MailChimp

In the previous chapter we discussed the use-case of syncing data changes from MailChimp to the MS Dynamics CRM instance. In this use-case, you already have a database. You want to collect some or all of the information associated with your subscribers, preferably as it changes, and save it back to your database. What might you do with that information? Here are some options:

  • If you rely on having valid email addresses , you’ll want to get bounce and unsubscribe data.

  • You may have important information in our merge fields or interest groups that your systems rely on.

  • You want to know about new subscribers quickly and without parsing out emails.

Syncing to MailChimp

In the other hand in the present chapter we are going to discuss syncing database changes from You to MailChimp: In this usecase, you already have a database or list that will be modified or updated according to your standard business processes. But you also need to make sure some of that information is updated for your list members, especially before campaigns go out.

Here are the situations where this might apply:

  • You have a CRM. (Customer Relation Management) system that’s updated during your workday.

  • You offer a website where users register and manage a profile (an eCommerce site, blog, etc.)

It appears that Abtimo SARL find itself in both of the situations simultaneously. It has a Customer Relation Management system implemented with MS Dynamics CRM and it offers to the customers a way to create accounts by registering their version of mobile Apps distributed via Apple AppStore, Google Play, Microsoft Marketplace and Black Berry App Store. In order to simplify overall design let’s take assumptions that we will share common key between all three systems. For this purpose the email will be the unique identifier for contact in registration database, subscriber in MailChimp and Account in the Dynamics CRM. This assumption allows us to avoid using additional database tables to manage relationship between identifiers. By comparison with IBM BPM Advanced ESB mediations or BPEL short running processes, the IIB  v.9 lack database nodes able to manage it for us automatically, so we need manually implement relationship tables and all necessary programming logic. And whatever the implementation, having additional tables to manage and populate has its own drawbacks. Another assumption is the fact that failure to synchronize data during network or hardware outage is supposed to be unlikely event and can be cleaned manually, so we are not going to implement complex compensation scenarios. We will implement the main scenario, of direct creation of Accounts in Dynamics and will discuss one alternative, when the customer might already exists in the CRM backend.

Overall wiring diagram

As soon as the customer register his copy of App, whatever the platform, it becomes interesting to subscribe him to the mailing list and propagate his information into on premises CRM solution.

Client’s app and users table.

Every client app features registration screen. The screen appears in front of the customer upon the first program start. If the user cancels the registration he will be reminded later. The screen appears randomly after several launches of the app until the event of successful registration. In the meantime the registration screen is still available via Settings menu. The user asked to capture several fields with the information. All fields are optional except the email field. Upon completion of the form and successful sending data to the server the form disappears and the menu on setting page will be grayed out.

Alongside with captured data other important information are sent to the server, such as App name, version, device type, make, OS version, preferred language etc.

All this information arrives in the user table:

Field Type Key Extra
id int(14) PRI auto_increment
title varchar(255)
fname varchar(255)
lname varchar(255)
email varchar(255)
address1 varchar(255)
address2 varchar(255)
region varchar(255)
postcode varchar(255)
city varchar(255)
country varchar(255)
lang varchar(255)
os varchar(255)
UDID varchar(50)
device varchar(255)
appname varchar(255)
appversion varchar(255)
status varchar(255)
created_at datetime
updated_at timestamp on update CURRENT_TIMESTAMP

Main scenario implementation

The image below depict the message flow that implements the mail scenario.

Connecting to the MySQL database.

In our scenario the registration site implemented using LAMP technology. So the users table implemented on MySQL server and will be used remotely via ODBC connection. The MySQL is a default choice in most of hosting providers. Unfortunately we did not succeed with reverse engineering data from MySQL to create the database model dbm file required for node parameters setting and message set generation. That said with manual creation of dbm it worked as expected.

First of all we must download and install mysql-connector-odbc-5.3.4. and install it.

Open the ODBC management window and configure the datasource

Capture data, and test the connection.

Configure datasource on the broker level:

mqsisetdbparms BRKLOC01 -n <DATASOURCE> -u <username> -p <password>
mqsireload BRKLOC01 -e Z_UDEPLOY

Database input schema, event table and triggers.

In order to capture the rows creation in user table and to propagate them to back-ends afterwards we will use Database Input node.

First of all In order to function the database input node requires event table creation as well as triggers to populate it during updates of the users table.

CREATE TRIGGER `REG_USR_CREATE` AFTER INSERT ON `users` FOR EACH ROW INSERT INTO REG_EVENTS (OBJECT_KEY, OBJECT_VERB)

VALUES (NEW.id, ‘Create’)

Field Type Key Extra
EVENT_ID int(11) PRI auto_increment
OBJECT_KEY int(11)
OBJECT_VERB varchar(40)

Then in order to parameter the node the database schema needs to be reverse engineered or crafted manually.

Below you find  the parameters for the node:

GBO Mapping

Database input node scans event table and for each event the data is retrieved and sent for treatment. It contains the application table row and the verb corresponding to the event. In our case we handle only creations so we can create only one trigger and the verb analysis is then irrelevant.

At first we will map the database row ASBO to GBO using DBASBO2GBO mapping node.

In order to describe input data for the mapping we have to create users database message set, using the “Integration Bus – Message Set Development → Message Definition File From…” wizard and then to select the message definition source as “Database definition DBM file” and pick the dbm from data design project

For output data description we will take the Subscriber GBO xsd definition file.

JSON API payload preparation and formatting.

In order to subscribe a new subscriber to the MailChimp mailing list we have to use MailChimp API method called “lists/subscribe”.

See description:

https://apidocs.mailchimp.com/api/2.0/lists/subscribe.php

First of all we must get the api_key value of our account and list id of the list to subscribe to.

Finding the api_key:

Log in to maichimp

Go to “username combo box“-> Account->Extra->API keys

See the api_key value under section “Your API keys” or click on “Create a key”

Finding the list id:

Go to menu List

Pick your list

Menu Settings -> List name and defaults

Read the list number under the following statement on the page :

Some plugins and integrations may request your List ID.

Typically, this is what they want: 99z999zz99.

During the next steps, given our api key and list id we have to send the JSON message to the url

https://us3.api.mailchimp.com/2.0/lists/subscribe

Here “us3” subdomain corresponds to the “-us3”postfix of our api key, and this is important accordingly to MailChimp.

In order to prepare the payload, we will create xsd corresponding to the xml data equivalent to the required JSON structure. It is possible just to take json spec and manually write corresponding schema description. But the easiest way to do it is to convert some JSON complete data sample to xml and then generate the xsd from this xml. A bit cumbersome, but see below how it’s supposed to be.

First of all take the JSON payoad sample from maichimp API documentation:

{

"apikey": "example apikey",

"id": "example id",

"email": {

"email": "example email",

"euid": "example euid",

"leid": "example leid"

},

"merge_vars": {

"new-email": "example new-email",

"groupings": [

{

"id": 42,

"name": "example name",

"groups": [

"..."

]

}

],

"optin_ip": "example optin_ip",

"optin_time": "example optin_time",

"mc_location": {

"latitude": "example latitude",

"longitude": "example longitude",

"anything": "example anything"

},

"mc_language": "example mc_language",

"mc_notes": [

{

"note": "example note",

"id": 42,

"action": "example action"

}

]

},

"email_type": "example email_type",

"double_optin": true,

"update_existing": true,

"replace_interests": true,

"send_welcome": true

}

Convert it to the corresponding XML using a third party conversion tool.

<?xml version=”1.0″ encoding=”UTF-8″?>

<root>

<apikey>example apikey</apikey>

<double_optin>true</double_optin>

<email>

<email>example email</email>

<euid>example euid</euid>

<leid>example leid</leid>

</email>

<email_type>example email_type</email_type>

<id>example id</id>

<merge_vars>

<groupings>

<element>

<groups>

<element>…</element>

</groups>

<id>42</id>

<name>example name</name>

</element>

</groupings>

<mc_language>example mc_language</mc_language>

<mc_location>

<anything>example anything</anything>

<latitude>example latitude</latitude>

<longitude>example longitude</longitude>

</mc_location>

<mc_notes>

<element>

<action>example action</action>

<id>42</id>

<note>example note</note>

</element>

</mc_notes>

<new-email>example new-email</new-email>

<optin_ip>example optin_ip</optin_ip>

<optin_time>example optin_time</optin_time>

</merge_vars>

<replace_interests>true</replace_interests>

<send_welcome>true</send_welcome>

<update_existing>true</update_existing>

</root>

Then create MailChimpListSubscribeRequest.xsd based on the xml object hereabove with the same tool :

<xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>

<xs:element name=”root”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”apikey”/>

<xs:element type=”xs:string” name=”double_optin”/>

<xs:element name=”email”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”email”/>

<xs:element type=”xs:string” name=”euid”/>

<xs:element type=”xs:string” name=”leid”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”email_type”/>

<xs:element type=”xs:string” name=”id”/>

<xs:element name=”merge_vars”>

<xs:complexType>

<xs:sequence>

<xs:element name=”groupings”>

<xs:complexType>

<xs:sequence>

<xs:element name=”element”>

<xs:complexType>

<xs:sequence>

<xs:element name=”groups”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”element”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:byte” name=”id”/>

<xs:element type=”xs:string” name=”name”/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”mc_language”/>

<xs:element name=”mc_location”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”anything”/>

<xs:element type=”xs:string” name=”latitude”/>

<xs:element type=”xs:string” name=”longitude”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name=”mc_notes”>

<xs:complexType>

<xs:sequence>

<xs:element name=”element”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”action”/>

<xs:element type=”xs:byte” name=”id”/>

<xs:element type=”xs:string” name=”note”/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”new-email”/>

<xs:element type=”xs:string” name=”optin_ip”/>

<xs:element type=”xs:string” name=”optin_time”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”replace_interests”/>

<xs:element type=”xs:string” name=”send_welcome”/>

<xs:element type=”xs:string” name=”update_existing”/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

The only step more that we require is to manually add the required minOccurs and maxOccurs values that will match MailChimp’s API spec.

<?xml version=”1.0″?>

<xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” xmlns:xs=”http://www.w3.org/2001/XMLSchema”>

<xs:element name=”ListSubscribeRequest”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”apikey“/>

<xs:element type=”xs:string” name=”id” minOccurs=”1″ maxOccurs=”1″/>

<xs:element name=”email”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”email” minOccurs=”0″ maxOccurs=”1″/>

<xs:element type=”xs:string” name=”euid” minOccurs=”0″ maxOccurs=”1″/>

<xs:element type=”xs:string” name=”leid” minOccurs=”0″ maxOccurs=”1″/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”double_optin”/>

<xs:element type=”xs:string” name=”email_type” minOccurs=”0″ maxOccurs=”1″/>

<xs:element name=”merge_vars” minOccurs=”0″>

<xs:complexType>

<xs:sequence>

<xs:element name=”groupings”>

<xs:complexType>

<xs:sequence>

<xs:element name=”element”>

<xs:complexType>

<xs:sequence>

<xs:element name=”groups”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”element”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:byte” name=”id”/>

<xs:element type=”xs:string” name=”name”/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”mc_language”/>

<xs:element name=”mc_location”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”anything”/>

<xs:element type=”xs:string” name=”latitude”/>

<xs:element type=”xs:string” name=”longitude”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name=”mc_notes”>

<xs:complexType>

<xs:sequence>

<xs:element name=”element”>

<xs:complexType>

<xs:sequence>

<xs:element type=”xs:string” name=”action”/>

<xs:element type=”xs:byte” name=”id”/>

<xs:element type=”xs:string” name=”note”/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”new-email”/>

<xs:element type=”xs:string” name=”optin_ip”/>

<xs:element type=”xs:string” name=”optin_time”/>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element type=”xs:string” name=”replace_interests” minOccurs=”0″/>

<xs:element type=”xs:string” name=”send_welcome” minOccurs=”0″/>

<xs:element type=”xs:string” name=”update_existing” minOccurs=”0″/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

Use the GBO2MCAPI mapping node to prepare JSON data for submitting the subscription request:

Put the data under JSON parser using BuildJSONRESTRequest ESQL compute node :

CREATE COMPUTE MODULE REG2MC_MF_BuildJSONRESTRequest

CREATE FUNCTION Main() RETURNS BOOLEAN

BEGIN

– Just copy the Properties over

SET OutputRoot.Properties = InputRoot.Properties;

SET OutputLocalEnvironment.Destination.HTTP.RequestLine.Method = ‘POST’;

– Build the JSON message from the XML document

SET OutputRoot.JSON.Data = InputRoot.XMLNSC.ListSubscribeRequest;

RETURN TRUE;

END;

END MODULE;

Call subsription API with CallMCJSONAPI HTTP Request node.

CallMCJSONAPI Properties:

Web Service URL: https://us3.api.mailchimp.com/2.0/lists/subscribe

Request timeout : 180

HTTP Method : POST

HTTP vesion 1.1

SSL: TLS ( this is important because SSL is gone after The Heartbleed Bug discovery and otherwise MailChimp will reject you )

Response message parsing : JSON

The JSON payload that will be posted on server (with your values of course) :

{“apikey”:”f9999ea9a9ab9e99db1aa999f999c9fa-us3″,”id”:”99b999dc99″,”email”:{“email”:”root@internet.com”}}

And the response from the server, if everything is correct:

HTTP/1.1 200 OK

Server: nginx

Content-Type: application/json; charset=utf-8

Content-Length: 76

Vary: Accept-Encoding

Date: Wed, 01 Apr 2015 17:48:10 GMT

Connection: keep-alive

Set-Cookie: _AVESTA_ENVIRONMENT=prod; path=/

{“email”:”root@internet.com”,”euid”:”99a9d99999″,”leid”:”999999999″}

MS Dynamics CRM call.

For the main scenario we consider that each user created from users table is unique and we are going to send generated GBO to the input of MS CRM account creation message flow, instance from MS CRM Pattern, see the first part of the article.

Alternative scenario approach

Please see here the implementation of the alternative scenario with MuleSoft.com Mule ESB

Let’s describe the solution that we must implement in order to handle situations when the Account already exists in the CRM database. As stated before, we assume that the first email of the Account entity will be unique for each Account. If the Account with the same email exists in the database we should update the account instead of new creation.

In order to implement this functionality we propose to create GBO2MSFCRU_MF (for GBO to Microsoft Find Create Update…)

The functionality is as follows:

Fore each GBO we will do look-up of the accounts ID with the same value of first email field.

If nothing is found, we will send the GBO for creation input queue ( MQ Input Create of MQToCRM message flow) .

If only one instance is found, we will take the Account id, add to the payload and send the GBO to the update input queue.

Otherwise we will send GBO to the error queue for manual processing.

The lookup can be done in many different ways. The two of them that do not require JDBC database connection nor Java usage are first of all, lookup of the accounts id using ESQL code tht using the query like:

select t.AccountId from dbo.AccountBase as t where t.EMailAddress1 = <email> ;


Connecting to the MS SQL Server

To be able to acces the MS CRM database we have to create ODBC datasource:

Launch ODBC management widget :

The easiest way of accessing this is by using the Run window and opening

%windir%\SysWOW64\odbcad32.exe;

System DSN -> Add -> Choose driver

Put values for name and server

Define authentication method:

Change the default database name :

Hit Next and Finish.

Microsoft SQL Server Native Client Version 10.00.2531

Data Source Name: MSDYNCRM

Data Source Description: MSDYNCRM

Server: WSRV2008R2X64VL

Use Integrated Security: Yes

Database: ABTIMOCRMDB_MSCRM

Language: (Default)

Data Encryption: No

Trust Server Certificate: No

Multiple Active Result Sets(MARS): No

Mirror Server:

Translate Character Data: Yes

Log Long Running Queries: No

Log Driver Statistics: No

Use Regional Settings: No

Use ANSI Quoted Identifiers: Yes

Use ANSI Null, Paddings and Warnings: Yes

Test the new datasource:

Microsoft SQL Server Native Client Version 10.00.2531

Running connectivity tests…

Attempting connection

Connection established

Verifying option settings

Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!


Done !

Calling MS Dynamics CRM .NET API

Alternately, another possibility is implementing .NET Compute node and calling the MS Dynamics CRM SDK API method:

https://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.messages.retrievemultiplerequest%28v=crm.6%29.aspx

[DataContractAttribute(Namespace="http://schemas.microsoft.com/xrm/2011/Contracts")]

public sealed class RetrieveMultipleRequest : OrganizationRequest

Route node sample

In the implemented message flow the Route node implemented for demonstration purpose it routes the GBO for prrocessing trough ESQL or .NET depending on the length of the email, being of odd or even number of characters.

The last step required is to implement mappings for the update lane of the MS CRM Pattern instance.

Setting up relationship tables

One of the patterns that solves the problem of ASBO identifiers correspondance is the creation of so called relationship table, where each record contains primary keys for all related ASBO of the backends to integrate. In this case before new Account creation we must initiate a record in the relationship table and upon succesful creation of the Account in the CRM database retrieve the Account ID and send it back to complete relationship record.

Leave a Reply

You must be logged in to post a comment.



 
       
iBudget    |   No More Money Falling Through Your Fingers        Home page
Popular Posts

Sorry. No data so far.

 
App store
Available on the iPhone
 
    Why Using Feedburner in Blogs Is So Important?