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

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.



  1. Download and install Data Sync.
  2. Read the Getting Started guide to Data Sync, to become familiar with the system.
  3. Access to a SQL server or a file system to create csv files.
  4. Access to a Dynamics CRM Online or On-Premise instance with sufficient permissions to create and update records.
  5. Sample contact records, which you can download from BrianDunning.
  6. Optional: A copy of Microsoft Excel, this will make the importing an exporting of excel documents much easier.


To get the most out of these tutorials you will 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 of excel will make some of the string, number, date and formula tutorials more accessible. If you do not already know how to connect to Dynamics CRM within Data Sync, please visit the Dynamics CRM page.


Data Sync can be installed on Windows 7, 8 and 10 and is less than 40 MB's to download. 8 GB RAM and a 4 core CPU is recommended. For best performance use a SSD storage disk.


If you are facing an issues and you need assistance you send an email to or use our online help




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.


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


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


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

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.<o:p></o:p>


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.


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

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) Connect


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.



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.

Data Source A

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

Add and Remove Columns

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.

Key Column

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



DS3 can connect to three different Dynamics CRM configurations: 1.Dynamics CRM 2013 (On-Premise) 2.Dynamics CRM 2011 (On-Premise) 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


Connection Settings for On-Premise Connection


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

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

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

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

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


Connection Settings for On-Premise Connection


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

For dynamics online your setting will be similar to:

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

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

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.



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

Drag Drop

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


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: Mapping

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: Compare A to B

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:

Compare Results

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

Green +


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:


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


Pressing 'Start' will begin the synchronization:



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:

Compare Results


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:

Data Source A

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.

Drag and Drop

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'

Connection Tree

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:


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.


Create a CSV File to Contact Project using the same file

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


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.

Drag and Drop

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

Lookup Connection

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.

Lookup Columns

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


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

Preview A

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:


For really advanced lookups you might want to consider using the LOOKUP functions inside the 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 modelled 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:

Calculated Columns

This will show you the Calculated Column Dialog Box:

Dialog box

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:

Calculated Column

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

Calculated Column

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




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)

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.

Calculated Column

The functions are fully documented in the online documentation and autocomplete feature of DS3:

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.



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.


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.

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.

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 you hardware 10

Dynamics CRM 2011 and Dynamics CRM 2013 and onwards 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 you hardware 100


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.

Create Task

This will display the 'Create Scheduled Task' dialog.

Create Scheduled Task

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:


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


Open the 'Task Scheduler' manager in Windows


Double click the Data Sync Task to edit the task schedule


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


Can't find the answers you're looking for? Contact our support team and we'll personally get back to you as quickly as we can.