Using Scribe DTS Parameters to drive your Dynamics CRM to Dynamics AX Integration

The Power of Scribe DTS Parameters

The Power of Scribe DTS Parameters

So I recently completed a very complex global deployment of Dynamics CRM 4.0 that had to be integrated to 3 different ERP systems! We have Dynamics GP 9.0, Dynamics AX 2009, and an old legacy Informix based ERP system.

We’re using Scribe insight 7.2 and the CRM 4.0 and AX adapters. (Upgrading is not yet an option, but planned)

The first issue among many was how to drive a single CRM system to multiple instances of Dynamics AX and Dynamics GP?

Those of you very familiar with Scribe, may know that the latest version of Scribe and the Dynamics CRM 2011 adapter, really make this whole scenario much easier with the filtering ability of the CRM 2011 publisher, but it was not an option here.

Well, the GP issue was not that bad because it was a single instance of GP with multiple GP companies. Scribe already has a nifty /TD parameter for specifying which GP database your DTS should connect to. That works fine.

But now with AX, the problem got a bit bigger…

This client has multiple AX servers throughout the world which means our Scribe target connection AX endpoint will need to be changed dynamically. Likewise, we may also need to change the target AX Company as well for a given endpoint, and finally to add a bit more complexity, we want to be able to pass orders from CRM to AX, but rather than having all the order and line item details in the XML message we get from the CRM publisher, we need to instead look-up the order in CRM and then process the line items for that order and send them to AX. So now we also need to know the CRM Order number!

Why do we need to do this? Well, as it turns out, this client happens to create orders in CRM with hundreds of line items. Each line item in CRM has hundreds of fields of data. When the CRM publisher generates these XML messages they can become VERY large, and we have found that they will exceed the size limitation of the Microsoft Message Queue, so processing orders with more than 20 or so line items in this case will fail miserably.

Of course, to keep this as simple and efficient as possible, we want to use a single DTS file with minimal steps and complexity.

This is where we can take advantage of the power of the Scribe DTS Parameters field in our Integration Process to handle the situation perfectly!

So here is our high level architecture…

1 – Scribe Dynamics CRM 4.0 publisher configured for the salesorder entity inserts and updates. This will publish XML messages to the Scribe in-queue each time a CRM order is entered or updated.

2 – Two custom fields in CRM on the Order Entity. my_AXInstanceURL and my_AXCompany.  These values are pulled with some code from the “AX Company” values that match the Account on the order.

3 – A new custom CRM Entity called “AX Company”. This entity holds the AXInstance URL, which is the AX endpoint URL for the various AX instances, and AXCompany, which holds the name of the AX Company for that instance such as MDCG. Each account record in CRM is assigned an AX Company if in fact it is an account being handled in AX.

4 – A message queue integration process that watches for our order messages and then will run a DTS that creates the Order Header in AX. Once that DTS is done, the message is placed back into the Scribe In-Queue with a message label of “CRMOrderLines”.

5 – A message queue integration process that watches for the “CRMOrderLines” message and will run our order line DTS, which will be the focus of the rest of our tutorial.

6 – An Order header DTS. This DTS takes our initial order message and pushes the header details to AX based on our design mapping requirements.

7 – An Order Line DTS. This is the DTS we’ll be focusing on below. It will take an input CRM Sales Order ID and process all the line items for that order into AX for the matching parent order.

So here is where the bulk of our magic begins.

Our OrderLine processing DTS will use a source query into Dynamics CRM so that we can grab all the order lines associated with our order that we just processed the header info to AX.

To accomplish this, we need to pass the CRM salesorderid to our DTS using a DTS Parameter string in our integration process that runs the DTS, once we have this value we can use that in our custom source query. Let’s have a look at some setup of our DTS to handle this. (We’ll work on the integration process and the actual DTS Params later)

So I created a new DTS and called it CRMOrderLinesToAXOrderLines.dts. I use this naming convention a lot as it makes it easy for me to know the process of the DTS without opening it.

The first thing I’ll do is create a “Prompted Variable” in the new OrderLine DTS. I’ll call it OrderID to keep it simple as in the Screen shot below.

For the “Default Value” you can copy any legitimate CRM SalesOrderID and paste the GUID into this box as shown. This is an important step because when you open your DTS in the workbench it will use the values found in this order to load your DTS test window with data. You can also use it for troubleshooting later. (I usually grab this value from a message in the queue.)

Scribe Prompted Variable Settings

Scribe Prompted Variable Settings

I save this new Prompted variable and we can move on to creating the source query for the DTS.

One of the more powerful and useful features of Scribe Insight is the ability to use a SQL query for the result set that will be returned to your DTS. Don’t worry if you’re not familiar with SQL and creating DB queries, as the Scribe workbench has a nifty wizard that we can use to pretty quickly create the query we need.

I’ll assume that you have already specified your connections for this DTS and you’ve successfully created a connection for Dynamics CRM as well as Dynamics AX.

So now let’s go ahead and configure the source for the DTS by clicking on the “Configure Source” button.

After selecting Dynamics CRM Adapter as my source, I click the “Custom Query” radio button and the query wizard will open as below.

Scribe DTS Source Configuration Wizard

Scribe DTS Source Configuration Wizard

In this window, we will want to select our Primary Data object to be the “SalesOrderDetail” entity. This is our order lines entity in CRM. Next I select “SalesOrder” as my Parent entity. Be sure to check off the box “Required”. This will insure that we always get order lines for only the order ID that we provide.

That’s looking great, so now I click on the “Fields” tab in the wizard window and tell the wizard I want all the fields for both entities. See the shot below.

Scribe DTS Source Configuration Wizard 2

Scribe DTS Source Configuration Wizard 2

OK, we’re almost there…

Next I click on the “Filters” tab. This is where I need to tell the query that I want it to use the orderID that I’ll be passing into the DTS at runtime from the DTS Parameters I’ll be supplying in the Integration process a little later.

This is where we get to use our newly created Prompted Variable.

Click on the “Add Condition” button and use the drop down windows (Like creating a CRM workflow), to specify the filter criteria. It should match the window below.

Scribe DTS Source Configuration Wizard 3

Scribe DTS Source Configuration Wizard 3

Great. Now I click OK and then click the Save Query Button.

That’s all the setup we need to do for our DTS. I’ll assume that you already can do the rest like create your target AX steps and add you mappings. That process is exactly the same as if your source was an XML message file.

Next is the integration process that will be used to fire off the OrderLine DTS.

This is a standard integration process just like any other that monitors the message queue for a message meeting a certain criteria. In this case, if you recall, after I run the Order Header DTS, I put the message back into the queue with a new label of “CRMOrderLines”, so this Integration process will look for that message label and when found will run my new “CRMOrderLinesToAXOrderLines.dts”.

Now here is the BIG deal! Our DTS Parameter string…

Scribe DTS Parameter Window

Scribe DTS Parameter Window

So what this string does is uses XPATH to parse through our XML message and extract the values we need from the message and then pass them into the DTS at runtime.

The first parameter /VOrderID=”%XPath=//salesorderid” tells the system to grab the salesorderid from our XML message and pass that into the DTS as our prompted variable called “OrderID”. That is what the /V switch does. It allows you to substitute a user variable.

The next parameter /TS=%Xpath=//my_axinstanceurl tells the system to substitute the Target Source value (/TS) with the value stored in my_axinstanceurl field.

Finally the last parameter /TA=companyname=%Xpath=//my_axcompany tells the system to substitute the “Additional Target” value of “companyname” with the value in my_axcompany field. This is the company name parameter normally specified in the target connection window for AX where you need to have the AX endpoint and the AX Company.

So that’s it!

If we wrap it all up and do some testing we will see that based on the values we have stored in CRM we can easily and reliably drive our integration to multiple AX instances and companies using this method and the powerful DTS Parameters.

Thanks for checking it out and let me know if you have any questions!

Also let me know if there was too much detail or not enough so I can better create future posts. And as always if there is a particular topic you would like me to cover, or you are having a problem with something, let me know and I will create a post to help.

Happy Integrating!

Don