Dynamics AX 2012 Integration using AIF: Microsoft Dynamics AX 2012 Master Data Management Scenario (Part 2)

Microsoft Dynamics AX 2012 Master Data Management Scenario (Part 2)

Purpose: The purpose of this document is to provide a walkthrough of how implement Master Data Management scenario for Microsoft Dynamics AX 2012. Please note that in the previous article I described how to do initial configuration of Microsoft SQL Server 2012 Master Data Services for Microsoft Dynamics Master Data Management scenario.

Challenge: The pain that organizations are experiencing around data accuracy, consistent reporting, regulatory compliance, etc. generated a lot of interest in Master Data Management. Master Data Management becomes extremely important when you have multiple different systems and you have to manage the data in different systems, each system may potentially have its own version of the same data which means not only to duplication of the data but also to data inconsistencies and data conflicts. Master Data Management is a solution in this case with ultimate goal to have a single version of the truth in data terms. The main functions of Master Data Management are content management, relationship management, access control, change management and data processing.

Solution: In order to resolve Master Data Management challenges Microsoft offers an excellent solution which is based on Microsoft SQL Server platform. SQL Server Master Data Services provides a central data hub that ensures the integrity of information and consistency of data is constant across different applications. In this document I’ll explain how to build a solid Master Data Management solution leveraging Microsoft SQL Server, SQL Server Master Data Services and SQL Server Integration Services.

Scenario

Microsoft Dynamics AX 2012 and Microsoft Dynamics CRM 2011 have some built-in Master Data Management capabilities, however in this document I’ll focus on a more generic scenario when we have not only Microsoft Dynamics products in play but also some other systems. For the sake of simplicity in this document I’ll consider usual Master Data Management entities such as Customers and Products.

Walkthrough

In this walkthrough I’m using Microsoft Dynamics AX 2012 R2 Demo VM (AX2012R2A) which has Microsoft SQL Server 2012 installed. Once all appropriate infrastructural components are installed and configured we’ll have to consume the data which is centrally managed in Master Data Services and synchronize it to Microsoft Dynamics AX 2012. In particular I’ll implement SSIS project which will grab the data from Master Data Services using Subscription Views and synchronize the data into Microsoft Dynamics AX 2012 using Web Services.

Let’s get started!

One of prerequisite steps before we will be able to create Customer with specific Account number in Microsoft Dynamics AX 2012 would be to change Number sequence setting for Account number to Allow user changes

Number sequences – Account number

The data from Master Data Services will be synchronized into Microsoft Dynamics AX 2012 by means of Web Services, that’s why we need to activate appropriate Inbound ports. In this instance I manually created Enhanced Inbound port “DynamicsServices”. Please note that Adapter = HTTP

Inbound ports

And included all required Web Services operations for Customer account and Product definition creation

Select Service operations

Now we can review Configuration details associated with Inbound port. In this instance we’ll be interested in using basicHttpBindingWithWindowsAuth binding with HTTP Adapter

Configuration (Configure)

Configuration (Configure AOS)

Once Inbound port is activated you can also review the details of deployed Web Service. RoutineService Service gets deployed when you use HTTP Adapter

RoutingService Service

You can also review the details of WSDL associated with Web Service

WSDL

Now once we have all infrastructure ready from Microsoft Dynamics AX 2012 side we can start with Integration Services Visual Studio project

Visual Studio – Dynamics AX

The first step would be to add Data Flow Task which will contain actions to grab the data from Master Data Services and synchronize it to Microsoft Dynamics AX 2012 by means of Web Service call

Data Flow Task

Within Data Flow Task I’ll add OLE DB Source component which will help me to grab the data from Master Data Services using Subscription View

OLE DB Source

Now I’ll have to add OLE DB Connection and specify the location of MDS database, name of Subscription View, etc. I assume that Integration User who will be accessing business data in Master Data Services through Subscription View will have appropriate permissions set up in Master Data Services

Configure OLE DB Connection Manager

In this instance I’ll use Windows Integrated authentication and access MDS database on the same server

Connection Manager - Connection

Configure OLE DB Connection Manager

Now LocalHost.MDS OLE DB Connection Manager has been created and I can proceed to the next step

Connection Manager

Next step would be to define where I’ll grab Customer and Product data from in Master Data Services database. And this is what I will specify below by referring to “DynamicsCustomer” Subscription View in MDS database

OLE DB Source Editor – Connection Manager

Having appropriate permissions now you can see Customer data exposed from Master Data Services by means of Subscription View in external application

Preview Query Results - Customer

I’ll select all available columns to have access to all Customer data

OLE DB Source Editor – Columns

OLE DB Source Editor – Error Output

Similarly we can set up OLE DB Source component to read Product data from Master Data Services

OLE DB Source Editor – Product

Preview Query Results – Product

OLE DB Source Editor – Columns

OLE DB Source Editor – Error Output

Now OLE DB Source component is set up to read the data from Master Data Services

OLE DB Source – Done

And the next step is to add Script component which will help us to take business data from OLE DB Source component and synchronize it to Microsoft Dynamics AX 2012 by means of Web Services. I’ll drag and drop Script component to the canvas and select Component Type = Transformation

Select Script Component Type

Now we have to configure Script component

Script Component

But before doing this we’ll connect OLE DB Source component and Script component to pass the data through

OLE DB Source + Script Component

Next step is to configure Script Component

Script Transformation Editor – Script

For the sake of simplicity what I really need is just a Name (Customer Name or Product Name)

Script Transformation Editor – Input Columns

Script Transformation Editor – Inputs and Outputs

Script Transformation Editor – Connection Managers

Finally I’ll click Edit Script button in order to implement the code which will read the business data and invoke Microsoft Dynamics AX 2012 Web Service for data synchronization. This is how the project looks like initially

VstaProjects – C#.NET project

Now as usually we can add Web Service Reference to C#.NET project

Add Service Reference – CustomerService

Add Service reference - EcoResProductService

Once Web Service Reference is added C#.NET project will look like this. Please note that I added using XYZ.DynamicsAX; statement in Namespaces region

VstaProjects – C#.NET

Now let’s review the simple implementation of Microsoft Dynamics AX 2012 Web Service call in SSIS Script Component C#.NET project

Source code - Customer

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using SC_9bef93d9b7ca45bd9f9d44ac25c89d32.DynamicsAX;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

CustomerServiceClient client;

CallContext context;

AxdCustomer customer;

EntityKey[] entityKey;

public override void PreExecute()

{

base.PreExecute();

client = new CustomerServiceClient();

context = new CallContext();

customer = new AxdCustomer();

customer.CustTable = new AxdEntity_CustTable[1];

}

public override void PostExecute()

{

base.PostExecute();

try

{

entityKey = client.create(context, customer);

}

catch (Exception e)

{

throw;

}

public override void Input0_ProcessInputRow(Input0Buffer Row)

{

customer.CustTable[0] = new AxdEntity_CustTable();

customer.CustTable[0].AccountNum = "Dynamics";//Row.Name

customer.CustTable[0].CustGroup = "10";

}

For the sake of simplicity here I just process 1 single record, that’s why I hard-coded array element “0” in Input0_ProcessInputRow method which will be executed for every Customer record retrieved

Source code – Product

<TBD>

Finally you will Rebuild the project and save changes to SSIS Script Component. And now we’ll be ready to execute SSIS package

VstaProjects – C#.NET

I tried to execute this in Debug mode first and I got the following Error

Script Component: Runtime Error

The reason why I got this error is because SSIS Runtime doesn’t know about “DynamicsAX.CustomerService” Web Service configuration yet. In order to provide Web Service configuration details I’ll have to modify the following files in …\Microsoft SQL Server\110\DTS\Binn folder

<![if !supportLists]>- <![endif]>DTExec.exe.config

<![if !supportLists]>- <![endif]>DTSDebugHost.exe.config

<![if !supportLists]>- <![endif]>DTSHost.exe.config

File system

You can simply add Web Service Reference into fake C#.NET Console app project first and then copy <configuration> section from App.config into files specified above

Config: App.config

<?xml version="1.0" encoding="utf-8" ?>

<system.serviceModel>

</security>

</binding>

</security>

</binding>

</security>

</binding>

</basicHttpBinding>

</bindings>

<endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"

binding="basicHttpBinding" bindingConfiguration="reqReplyEndpoint"

contract="DynamicsAX.IRequestReplyRouter" name="reqReplyEndpoint" />

<endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"

binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_CustomerService"

contract="DynamicsAX.CustomerService" name="BasicHttpBinding_CustomerService" />

<endpoint address="http://ax2012r2a.contoso.com:89/MicrosoftDynamicsAXAif60/DynamicsServices/xppservice.svc"

binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_EcoResProductService"

contract="DynamicsAX.EcoResProductService" name="BasicHttpBinding_EcoResProductService" />

</client>

</system.serviceModel>

</configuration>

So now I’ll add configuration details into these files: DTExec.exe.config, DTSDebugHost.exe.config and DTSHost.exe.config

Config: DTExec.exe.config

</startup>