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
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.
