Microsoft Dynamics CRM 2011 Master Data Management Scenario (Part 3)

Microsoft Dynamics CRM 2011 Master Data Management Scenario (Part 3)

 

Purpose: The purpose of this document is to provide a walkthrough of how implement Master Data Management scenario for Microsoft Dynamics CRM Online. 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. I also signed up for free trial Microsoft Dynamics CRM Online and created my own company for the purposes of this article. The plan is still to centrally manage master data in Master Data Services and synchronize it to Microsoft Dynamics CRM Online this time.  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 CRM 2011 using Web Services.

 

Let’s get started!

 

As I mentioned before I signed up for free trial Microsoft Dynamics CRM Online which means I’ll be using Cloud version of Microsoft Dynamics CRM as opposed to On-Premises version of Microsoft Dynamics CRM. This will add some nuances around Authentication because Microsoft Dynamics CRM Online uses Office 365 authentication mechanism which is based on Windows Azure Active Directory Services

 

Here’s CRM Organization which I created

 

Microsoft Dynamics CRM Organization

 

The data from Master Data Services will be synchronized into Microsoft Dynamics CRM Online by means of Web Services, that’s why we need to find the way to consume Microsoft Dynamics CRM Online

 

For the sake of simplicity I used Quick Start project which comes with Microsoft Dynamics CRM SDK, in particular Quick Start with Simplified Connection, and simplified it to meet my needs

 

Quick Start project in CRM SDK

 

 

There’re several options how to connect to Microsoft Dynamics CRM 2011. In this particular instance I’ll be connecting to Microsoft Dynamics CRM Online that’s why I’ll need to use connectionString which corresponds to “Online using Office 365” option. You can find examples of connectionString’s in app.config file which is a part of Quick Start with Simplified Connection project

 

Config: app.config

 

<?xml version="1.0"?> <configuration>   <connectionStrings>     <!-- Online using Office 365 -->     <add name="Server=CRM Online, organization=contoso, user=someone"     connectionString="Url=https://contoso.crm.dynamics.com; Username=someone@contoso.onmicrosoft.com; Password=password;"/>       <!-- Online using Windows Live ID -->     <!-- <add name="Server=CRM Online, organization=contoso, user=someone@hotmail.com"          connectionString="Url=https://contoso.crm.dynamics.com; Username=someone@hotmail.com; Password=password; DeviceID=11hfn41bbqrg580vyvoea05abc; DevicePassword=fuqNIlx%e$.l*+ax_#8O4abc;"/>-->         <!-- On-premises with provided user credentials -->     <!-- <add name="Server=myserver, organization=AdventureWorksCycle, user=administrator"          connectionString="Url=http://myserver/AdventureWorksCycle; Domain=mydomain; Username=administrator; Password=password;"/> -->       <!-- On-premises using Windows integrated security -->     <!--<add name="Server=myserver, organization=AdventureWorksCycle"          connectionString="Url=http://myserver/AdventureWorksCycle;"/>-->       <!-- On-Premises (IFD) with claims -->     <!--<add name="Server=litware.com, organization=contoso, user=someone@litware.com"          connectionString="Url=https://contoso.litware.com; Username=someone@litware.com; Password=password;"/>-->   </connectionStrings>   <startup>     <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>   </startup> </configuration>

 

Before moving forward I tested Quick Start with Simplified Connection project and verified that I can successfully connect to Microsoft Dynamics CRM Online as well as do data manipulation (create records, update records, etc.)

 

Run Quick Start with Simplified Connection project

 

 

Please note that Microsoft Dynamics CRM 2011 exposes multiple Service endpoints: SOAP, REST. However in this walkthrough we’ll not deal directly with Service endpoints and we’ll rely on Microsoft Dynamics CRM SDK DLLs which encapsulate business logic for Web Service calls. These DLLs are also included in Quick Start with Simplified Connection project

 

Developer Resources

 

 

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

 

Visual Studio – Dynamics CRM

 

 

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 CRM Online 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

 

 

Please note I’m reusing OLE DB Connection Manager created in the previous article for Microsoft Dynamics AX 2012

 

Now LocalHost.MDS OLE DB Connection Manager has been selected 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

<![if !vml]><![endif]>

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 References to Microsoft Dynamics CRM 2011 SDK DLLs to C#.NET project

 

Add DLLs References – CRM

 

 

Please see the list of added Microsoft Dynamics CRM SDK DLLs on the screenshot above. In particular they are

<![if !supportLists]>-          <![endif]>Microsoft.Crm.Sdk.Proxy.dll

<![if !supportLists]>-          <![endif]>Microsoft.Xrm.Client.dll

<![if !supportLists]>-          <![endif]>Microsoft.Xrm.Sdk.dll

 

After DLLs References were added C#.NET project I also added appropriate using Microsoft.XYZ; statements in Namespaces region

 

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

 

Source code – Account

 

using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper;   using System.Configuration; using System.ServiceModel;   using Microsoft.Crm.Sdk.Messages;   using Microsoft.Xrm.Sdk; using Microsoft.Xrm.Sdk.Query;   using Microsoft.Xrm.Client; using Microsoft.Xrm.Client.Services; using System.Collections.Generic;   [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent {     public override void PreExecute()     {         base.PreExecute();         /*          * Add your code here          */            }      public override void PostExecute()     {         base.PostExecute();         /*          * Add your code here          */     }       public override void Input0_ProcessInputRow(Input0Buffer Row)     {         String connectionString = "Url=https://contoso.crm.dynamics.com; Username=someone@contoso.onmicrosoft.com; Password=password;";         Run(connectionString);     }       public static void Run(String connectionString)     {         try         {             Guid accountId;             OrganizationService service;                         Microsoft.Xrm.Client.CrmConnection connection = CrmConnection.Parse(connectionString);               using (service = new OrganizationService(connection))             {                                                                Account account = new Account { Name = "Alex Anikiev" };                               accountId = service.Create(account);                                            }         }         catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)         {             throw;         }     } }

 

For the sake of simplicity here I just process 1 single record, that’s why I override only 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

 

Package Validation Error

 

 

The reason why I got this error is because SSIS Runtime can’t locate referenced Microsoft Dynamics CRM SDK DLLs. In order to resolve this issue I’ll have to register referenced DLL assemblies in GAC

 

cmd: gacutil

 

 

And also copy referenced DLL assemblies into …\Microsoft SQL Server\110\DTS\Tasks folder, so I can successfully run SSIS Package in Debug mode. Here’s the list of referenced DLL assemblies:

<![if !supportLists]>-          <![endif]>Microsoft.Crm.Sdk.Proxy.dll

<![if !supportLists]>-          <![endif]>Microsoft.Xrm.Client.dll

<![if !supportLists]>-          <![endif]>Microsoft.Xrm.Sdk.Deployment.dll

<![if !supportLists]>-          <![endif]>Microsoft.Xrm.Sdk.dll

 

File system

 

 

When I try to run my SSIS package again now I’ll get “Package execution completed with success” message

 

Dynamics AX – Run package (Debug)

 

 

After successful execution of SSIS package we can verify the result in Microsoft Dynamics CRM Online. As the result we’ll have Account (and Product) created in Microsoft Dynamics CRM Online

 

Dynamics CRM – Account

 

 

Dynamics CRM – Account Details

 

 

Dynamics CRM – Product

<TBD>

 

Please note that for the sake of simplicity in current implementation of SSIS Script Component I only do creation of Account (and Product). However you can obviously implement business logic to update existing Account (and Product) if it already exists in Microsoft Dynamics CRM Online

 

The very last step for us is to deploy SSIS package, so we can use it in production. For these purposes we’ll use Deployment Wizard

 

Deployment - Introduction

 

 

Please note that I have already created SSISDB catalog in the previous article related to Microsoft Dynamics AX 2012

 

Now I’ll create a folder for DynamicsCRM

 

Create New Folder

 

 

So now I have created DynamicsCRM folder

 

Browse for Folder or Project

 

 

And I can proceed with deployment of my SSIS package

 

Deployment – Select Destination

 

 

Deployment - Review

 

 

Deployment - Results

 

 

So now when I’m done with deployment of my SSIS package I can export package/folder from Integration Service Catalog, import it into SSIS instance and set up for periodic execution or execute package right away

 

As the result you will be able to consistently manage Customer and Product master data in Master Data Services and continuously synchronize it into Microsoft Dynamics CRM Online using SSIS package we have just created

 

Summary: This document describes required minimum implementation of SQL Server Integration Services package to synchronize master data from Master Data Services into Microsoft Dynamics CRM Online. In particular I explained the process of SSIS project creation, design, development and deployment to support Master Data Management scenario for Customers and Products. SQL Server Integration services provides a robust platform for data synchronization in Master Data Management scenario which allows us to create records, update records, do any kinds of data transformations, etc. as required. 

 

Tags: SQL Server 2012, Dynamics AX 2012, Dynamics CRM Online, Master Data Management, Master Data Services, Data Quality Services, SQL Server Integration services, Customers, Products.

 

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the issues and describe the solutions.

 

Author: Alex Anikiev, PhD, MCP