United Kingdom +44 (0) 1424 863 450   hello@simego.com

Integrating Microsoft Dynamics CRM with Simego DS3

Simego DS3 is designed to get you integrating Dynamics CRM with other systems within the first 30 minutes of use


10 December 2014

Often the most compelling way to introduce yourself to a software product is to try deliver value as soon as possible.

Simego DS3 is designed to get you integrating Dynamics CRM with other systems within the first 30 minutes of use. 


PREREQUISITES THESE TUTORIALS

 

Software

 

  • 1.     Download and install DS3 from /Install/Data-Synchronisation-Studio
  • 2.     Access to a SQL Server or a file system to create csv files
  • 3.     Access to a Dynamics CRM Online or On-Premise Instance with sufficient permissions to create and update records.
  • 4.     Sample contact records which you can download from here http://www.briandunning.com/sample-data/ )
  • 5.     Having a copy of Microsoft Excel will make the importing and exporting of Excel documents much easier.

 

Experience

 

To get the most out of these tutorials you would want to be familiar with Dynamics CRM and some of the concepts, such as parent records, Accounts, Contacts and a basic understanding of relationships. In addition an understanding the basics of excel will make some of the string, number, date and formula tutorials more accessible.

 

Desktop Hardware

 

DS3 can be installed on Windows 7, 8 and is less than 40 MB’s to download, 8 GB RAM and 4 Core CPU is recommended. For best performance use SSD Storage Disk.


ONLINE HELP AND OUR HELPDESK

If you are facing an issues and you need assistance you send an email to support@simego.com or use our online help https://www.simego.com/Help/

 


 

DS3 USER INTERFACE CONCEPTS

 

image

CONNECTIONS

The connection section allows you to store connections which you have previously created. This reduces the friction of connecting and authenticating (correctly) every time a new integration is required and allows easy to use features such as drag ‘n drop on lookups and option sets.

 

SOURCE

The source section on the left (Data Source A) defines what fields can be included when synchronizing to the destination.

 

DESTINATION

As with the source, the destination (Data Source B) defines possible fields that than can be synchronized to.

 

MAPPING

This section defines what fields of the source records will map to the fields of the destination records.

 

image MAPPING HAS OTHER EFFECTS

When we map fields together DS3 uses this information to decide when to create, update or delete records. Defining a primary or unique key is very important to these decisions.

 

 

TRANSFORMATIONS

DS3 supports Lookups, Case Statements, Formulas including a significant portion of the Excel formula base and a fully featured .NET C# development environment for advanced users.


 

CONNECTING TO A DATASOURCE (CSV FILE)

 

 

This introductory tutorial describes how to connect to a Data Source, how to include fields for synchronization and how to set a key column. In this tutorial we are using a csv file as a Data Source.

 

The sample csv file used contains 500 fictional US contacts from http://www.briandunning.com/sample-data/

 

To begin download this file to a local directory where you can load it into DS3.

 

Open DS3 and connect to the CSV File by clicking on the ‘Connect Data Source’ on the left (Data Source A)

 

image

 

image YOU CAN DRAG A CSV FILE FORM THE DESKTOP ONTO THE DATA SOURCE TO OPEN IT

When you use a file (csv, excel, xml) as a data source, it is not saved in the connection library but it can be open by just dragging it onto the Data Source.

 

 

The Connect to a data source dialog will display. Select the ‘Text File’ tab and then complete the file source details.

 

image

 

image DS3 CAN PROCESS MULTIPLE DATA FILES OF THE SAME TYPE

If you have 10 or 20 files with the same data fields inside, setting the file name to *.* or *.csv will indicate to DS3 to combine the contents into one set.

 

 

Once you have connected to the CSV file, DS3 will use the information in the CSV file’s header to create a list of possible fields to include in the synchronisation. The Columns are shown in the ‘Data Source A’ section on the left

 

image

 

Checking or un-checking fields in the data source tree will add and remove them from the synchronization

 

image

 

 

You can move a field up or down the list by selecting the row and pressing the ‘Move Up’ or the ‘Move Down’. In addition you can remove a field by selecting it and pressing the ‘Delete’ button in the toolbar.

 

Selecting Key columns by checking the field in the ‘Key Column’

The process of selecting key columns allows DS3 to reconcile the items in the source to the items in the target. Just as a social security number makes sure that no one mistakes one Joe Smith from another Joe Smith we have to choose a key or a set of keys that identify a contact. In this case the field email is unique.

 

image

 

Clicking the ‘Preview A’  button at the top toolbar will display all of the data currently selected for inclusion in the synchronization

 

If you select a key column which is not unique, DS3 will warn you in the Preview window (we have used firstname in this example)

  

image

 

CONNECTING TO DYNAMICS CRM

 

DS3 can connect to three different Dynamics CRM configurations:

  1. 1.     Dynamics CRM 2013 (On-Premise)
  2. 2.     Dynamics CRM 2011 (On-Premise)
  3. 3.     Dynamics CRM On-Line

 

DS3 supports all of these configurations utilising the same connection provider:

To open the connection dialog click on the green 'Connect Data Source' bar in the Source or Destination. This will display the ‘Connect to Data Source’ dialog. Click on the CRM tab at the top and then Microsoft Dynamics CRM 2011/2013 provider’.

 

Desktop Connecting to Dynamics CRM On-Premise

 

image

 

Connection Settings for On-Premise Connection

 

image

 

CrmAuthenticationProvider

In the case of a server hosted locally, this is set to ‘ManagedDomain

 

CrmServerURL

If you browse to your Dynamics CRM application you will see a similar URL:

In this case the server URL should be entered in this format: http://crm2013:5555

 

Organisation

If you have entered the previous information correctly then by clicking on the Organisation dropdown and select the Demo organization in the case.

 

Entity

If you have entered the previous information correctly, you should be able to use the entity dropdown to select an entity within the Dynamics CRM system.

 

Connecting to Dynamics CRM Online

 

dism /online /Enable-Feature:Windows-Identity-Foundation

 

image AUTHENTICATION

To connect to Dynamics CRM online Microsoft uses a feature called the WIF (Windows Identity Foundation 3.5). To enable this feature in Windows 8 type the following into a command line:

dism /online /Enable-Feature:Windows-Identity-Foundation.

When using Windows 7 or Windows 2008 server you will need to install WIF 3.5 runtime from Microsoft. See http://www.microsoft.com/en-us/download/details.aspx?id=17331

 

 image

Connection Settings for Dynamic CRM Online Connection

 

 image

 

CrmAuthenticationProvider

In the case of Dynamics CRM Online, this is set to ‘Federated’

 

CrmServerURL

For dynamics online your setting will be similar to: https://crm4.dynamics.com

 

Organisation

If you have entered the previous information correctly then by clicking on the Organization dropdown and select the Demo organization in the case.

Entity

If you have entered the previous information correctly, you should be able to use the entity dropdown to select an entity within the Dynamics CRM system

 

image SAVE THE CONNECTION IN THE CONNECTION LIBRARY

Once you have created a connection, you will be offered the chance to save it in the connection library. This is a great idea as you can then drag and drop entities and lookups at a later date without creating a new connection. The connection is stored in a file on your local system.


 

SYNCHONIZING DATA TO DYNAMICS CRM (Contacts)

 

Prerequisites

 

Follow the previous tutorial:

‘CONNECTING TO A DATASOURCE (CSV FILE)’

 

Then connect Data Source B (right hand side) to Dynamics CRM using the tutorial

‘CONNECTING TO DYNAMICS CRM’

 

Once connected, you should see a list of the fields from the source, but the Destination Columns in the mapping section will read <NONE> because we have yet to connect them together.

 

LEAVING A DESTINATION COLUMN MAPPED TO <NONE> IS A COMMON ERROR

If you attempt to compare of synchronize when a destination field is still set to <NONE> DS3 will display an error so it is always a good idea to check you don’t have a <NONE> fields before comparing.

 

You can map these to the target in several different ways:

 

Map by dragging and dropping the Data Source B column onto the “Destination Column’ in the ‘Schema Map’ Tab

 

image

 

 

Alternatively, map the columns together by the drop-down in the Destination column which contains all of the possible field you can map to.

 

image

 

Check the check-box in the Key Column in the email row to tell DS3 that the email address is a good way to uniquely identify a contact.

 

Once you have completed the mapping it will look something like this:

 

image

 

Note: email has been used as a key but care must be taken as this may not always be the case.

 

You can compare the Source and Destination by clicking ‘Compare A -> B’ on the tool bar:

image

 

This will load the data from the csv file and the contact entity and perform a reconciliation to identify missing or changed items in the target.

The reconciliation result is displayed in the in the Compare Results Panel:

 

image

 

Clicking on the (green +) will preview the rows to be added.

 

image

 

 

image DELETE IS DISABLED BY DEFAULT

When you first connect to a Destination System, DS3 automatically sets the EnableDelete Setting in the provider to false to make sure no items in the destination system can be deleted by accident. This can be changed to true if you want to delete items from CRM.

 

 

To synchronize the CSV file and the Dynamics CRM contact entity, click on the ‘Synchronise’ button in the taskbar:

 

image

 

A dialog will appear which allows you to choose whether to stop the processing in the event of any errors:

 

image

 

Pressing ‘Start’ will begin the synchronization:

 

image

 

image COMPARE AGAIN

When you have finished a synchronization it is always a good idea to check everything has been synchronized by comparing A->B again.

 

 

Once completed, you can check all of the fields match in the target system by reconciling them again by using the ‘Compare A-B’ feature.

 

There should be no records to update:

 

image

 

 

LOOKING UP VALUES IN OTHER ENTITIES

 

Managing relationships between entities in Dynamics CRM is a key requirement. In its simplest form the ability to set the parent of an account requires setting the parentcustomerid field in the contact to the accountid of the account. The question is how to lookup the accountid in the account based on some value in the source data.

 

We are using the source data from the ‘CONNECTING TO A DATASOURCE (CSV FILE)’ tutorial so reviewing this tutorial is a good idea.

 

In this tutorial we are going to import values into the account entity and then update the contact entity setting its parent to an account.

 

Review the data we are importing:

 

image

 

Our data file contains contact data but we are more interested in the ‘company_name’ which is a value we would prefer to import into the account entity.

 

Synchronize the account data into the account entity.

 

Open DS3 and set the Source A to the us-500.csv file (refer to the importing csv tutorial). The fastest way to do this is to drag the csv file from the file system onto the data source A.

 

image

 

If you have already saved a connection to your Dynamics CRM environment you can select the entity in the connections window, right click the entity and click on ‘Connect to Target B’. If you have not created a Dynamics CRM Connection please see tutorial ‘CONNECTING TO DYNAMICS CRM’

 

image

 

The Schema Map tab will show all of the fields from the source csv. Delete all of the mapping links by selecting them all and click the Delete button.

 

In the data source select the company_name and web columns. Map them to the account name and websiteurl fields in the destination:

 

image

 

Remember to select the company_name AND web as a key column because neither is unique by themselves.

 

 

Click the ‘Compare A->B’ to and view the results to see what is going to be Updated/Deleted/Inserted into the Dynamics CRM Account entity.

 

Synchronize the results to CRM

  

image

 

Create a CSV File to Contact Synchronisation using the same file:

  1. 1.     Connect the csv files on the source
  2. 2.     Connect the contact entity on the target and map the fields as below:
  3.  

image

  

Create a lookup based on the company name

Select the account entity from within the connections treeview and drag it onto the company_name column in the Data Source.

 

image

 

You will be presented with a dialog asking how the company_name column relates to the account entity dragged from the connections treeview?

 

image

 

In the dialog set the lookup Target column to ‘name’

 

We have essentially told DS3 that if you need the find details on the parent record of the contact, look for the account with the name the same as the company_name DS3 will now display a list of all the columns in the account entity you can bring into the mapping.

 

image

 

We can now use the associated accountid from the account lookup to fill the parentcustomerid in the contact record.

 

image

 

Preview the data by pressing Preview A and the Lookup1_accountid column will display the associated accountid’s.

 

image

 

You can now synchronize this data into the contact record and the contact and account will be associated.

 

To remove a calculated column, select it in the columns list and click the icon:

image

image

 

image USING THE LOOKUPB CALCULATED COLUMN FUNCTION

The drag and drop method using in this tutorial can be completed just using the LOOKUPB function: LOOKUPB("accountid", "account", WHEN("name", company_name))

For more details see the ‘USING CALCULATED COLUMNS’ tutorial.

 

TRANSFORMING DATA USING CALCULATED COLUMNS

 

A key feature of any integration solution is the ability to create new data by transforming existing information.

 

 

DS3 has the most advanced data transformation features of any integration product. Core to any integration system is usability and DS3 has modeled its key transformation technology ‘Calculated Columns’ on the formula field of a spreadsheet. The new columns can be used in a mapping like any other column.

 

To create a new Calculated Column

 

Connect to a data source and then select the ‘Calculated Column node in the columns list, then click the ‘fx’ or calculated column button:

 

image

 

This will show you the Calculated Column Dialog Box:

 

image


 

Static / Constant Values

 

In many cases you might want to create a fixed or static value such as the state of an entity. In this case just give the column a name, a type of int32 and then assign it a value of 0. This will create a new column called statecode.

 

In other cases might just be setting the statecode and statuscode to active:

 

image

See later on in the tutorial how to use a case statement to decide on the state and status codes.

 

image

 

image Dynamics CRM Data Types

It is always a good idea for the source columns and target columns to match Data Types. DS3 will actually transform data types from one type to another but the comparison might not make sense especially with date and number types such as decimal, integer etc.

 


 

String Formatting

DS3 has extensive string formatting but the following are popular functions:

  

Explicitly Formatting using the FORMAT function:

For example if you wanted to format the firstname columns and lastname columns into lastname, firstname

 

FORMAT("{0}, {1}",lastname,firstname)

 

In this case the first parameter is a string indicating that it should replace the first {0} with the first string that is passed and {1} with the next.

 

Applying Logic using a CASE statement

 

Often you need to choose a value based on a source column. In this case we can convert the salutation for Doctor to a smaller set of choices.

 

//Convert different Doctor to Dr.

CASE(Salutation,

Salutation, //This is the default if none of the values are found

WHEN("Doctor","Dr."),

WHEN("Dr","Dr."),

WHEN("MD","Dr."))

 

image Calculated Columns support comments

It is always a good idea to comment your calculated columns as you or someone else might need to extend their logic later.

 

Applying Logic using an IF statement

 

The IF statement is modelled on the Excel functional equivalent IF

 

//If the firstname is empty replace with (unknown)

IF(ISNULLOREMPTY(firstname),"(unknown)",firstname)

  

A complete and advanced string manipulation

To show the extent it is possible to manipulate and manage a complex transformation we are going to use the following example requirement:

 

‘A telephone number formatting function will not only handle the leading zeros, but if there are exactly 10 numeric characters in the source, it will also format it as (xxx) xxx-xxxx. If there are not exactly 10 characters in the source, the function will simply populate the raw value from your source.’

 

image

 

The functions are full documented in the online help and autocomplete feature of DS3:

/Help/Functions

 

Using Lookups to read other entities and Option Sets

Using DS3 we can lookup the value of an accounts accountid by passing its details to the LOOKUP function:

 

LOOKUPB("accountid", "account", WHEN("name", company_name))

 

LOOKUPB as opposed to LOOKUPA function is used to indicate that the B connection (Destination Connection) should be used rather than the Source (A).

 

When we want to lookup OptionSets we use a vertical | to separate the entity from its optionset.

Lookup the id in the accountrolecode OptionSet in the contact entity where the name was Influencer.

 

LOOKUPA("id", "contact|accountrolecode", WHEN("name", "Influencer"))


 

PERFORMANCE/TIME SETTINGS FOR THE DYNAMICS CRM PROVIDER

 

The Microsoft Dynamics SDK and the Web API it references has been through many iterations and optimizations. Simego has always taken advantage of these advances and has always strived to make using DS3 a simple but powerful experience.

 

We are going to review how to configure the Dynamics CRM Provider for optimum performance and reliability.

 

image

 

Date Time Handling

This setting controls how DS3 handles Dynamics CRM dates.

 

When set to ‘Local’ DS3 will convert the date time values in the source to UTC for processing on the server. When dates are read from the server they are sent back down and converted into local time for comparing with the local data sources.

 

When set to ‘UTC’ DS3 will not convert any dates read from the source files into local time and will not convert UTC date times from the server to local date times.

 

Note: Milliseconds are ignored when comparing dates

 

Page Size

The page size is the amount of rows read from CRM by DS3 at a time. A recommended value would be 5000.

 

Thread Count

DS3 can send requests to Dynamics CRM in parallel from a range of 1 to 128 at the same time. For example it can send two separate requests in parallel to insert data at one time and process the results. Using this setting can improve performance significantly when loading large sets of data. Dynamics CRM API instructions are processed by a webservers which are designed to processes many incoming requests at one time hence the high performance.

 

The problem with having a large Thread Count is that there is a startup time penalty between one or two seconds for 2 or 4 threads to 10 or 15 seconds for 64 or more threads.

 

DS3 is so fast that we would recommend only using more than 1 thread for lists larger than 1000.

 

Timeout

 

When Dynamics CRM processes inserts synchronously it waits until it has completed the inserts and associated plugins, business rules the time DS3 is waiting can exceed 00:03:00. Very rarely will you be required to change this value and it may only be when you received a Timeout exception.

 

UpdateBatchSize

 

Dynamics CRM can process batches of instructions in a single request. Meaning for each request you can batch up instructions for Insert/Update/deletes. This setting tells DS3 how many instructions to batch up at a time.

 

This setting is used in combination with the Thread Count to optimize the processing. The ability for Dynamics CRM to process data is completely dependent on your desktop and server hardware, DS3 will scale accordingly.

 

Some recommend settings for high performance integration.

 

Dynamics CRM Online Settings

 

Rows to be Processed

Thread Count

Update Batch Size

100

1

1

1000

2

50

10000

8

10

100000

32 / 64 / 128 Depending on your Hardware

10

 

Dynamics CRM 2011 / 2013 Settings

 

Rows to be Processed

Thread Count

Update Batch Size

100

1

1

1000

2

50

10000

8

250

100000

32 / 64 / 128 Depending on Hardware

100

 

SCHEDULING A SYNCHRONISATION USING WINDOWS SCHEDULER

 

 

Once you have created a Synchronisation with DS3 there is often a requirement to automate that Synchronisation. There are 3 ways to schedule a DS3 project:

1.     Use the ‘Create Scheduled Task’ from the DS3 Developer.

2.     Use the ‘Create Scheduled Task’ from the DS3 Run Tool.

3.     Use DS3 Automation Server (Ouvvi) to schedule a project that includes the DS3 project.

 

This tutorial will teach you how to schedule a single project from DS3 Developer edition and from the included DS3 Run Tool.

 

Use the ‘Create Scheduled Task’ from the DS3 Developer.

 

Open an existing DS3 project then select the Tools -> Create Scheduled Task from the menu.

 

image

 


 

This will display the ‘Create Scheduled Task’ dialog

 

image

 

If the ‘Data Sync Project to Schedule’ edit box is blank you may not have saved the project before opening this dialog.

 

You can now updated the setting to run periodically during the day. If you want a more complex schedule you can change this in a later step.

 

When clicking ‘OK’ you will be asked for your password so that the task can be run when you are not logged in:

 

image

 

Completing this ‘Set Password’ dialog will create a Scheduled Task within the ‘Task Scheduler’ subsystem of Windows.

 

image

 

Open the ‘Task Scheduler’ manager in Windows

image

 

Double click the Data Sync Task to edit the task schedule

 

image


 

You can open the Triggers tab and then the specific trigger to alter the schedule.

 

image

 


|
Can't find the answers you're looking for?