Data Import from SQL to MS Dynamics CRM

If you have worked with MS Dynamics CRM, you would have certainly needed to import data from your SQL databases. Integration with different servers come with their own challenges. In this blog, I will take you through a simple procedure of importing data from your SQL databases into your MS Dynamics CRM instance.

Before You Get Started

You will need to have a basic idea about how to create SQL queries and decent hands-on experience of MS Dynamics CRM.

Our Target

I needed to get all the entities and entity attributes from my MS Dynamics CRM and create a dynamic table for the selected entity and its attributes as table columns in the database. I also needed to insert multiple records into the table and finally import the same table into MS Dynamics CRM to get multiple entity record in a single click.

Solution

With the help of .net framework & Microsoft Dynamics CRM SDK, I developed a mapping tool for my requirement. This would essentially take care of the following scenarios:

1. Export & import with MS Dynamics CRM
2. Creating a dynamic table in our local database

Let's take a look at the screenshots:

Figure 1: Accept URL, Username, Password of the respective MS Dynamics CRM portal

Figure 2: The mapping screen will contain Entities, Entity attributes, SQL server
name and databases of selected server and a button which will create a dynamic table on selected database

Figure 3: Sync with Dynamics CRM button will import table records into Dynamics CRM

How I built my Rome

Step 1
The very first thing we need as per Figure 1 is a Dynamics CRM URL, Username and Password to connect with CRM. I have used IOrganizationService. With the help of this service, we can access data and meta data of the particular organization.

NOTE: For interaction purpose with Dynamics CRM, you need to add the following references:

Clicking on the button will give you the credential and you need to parse the credential by using Parse method of CRM connection class to pass it to IOrganizationService. CRM Connection is a class which is present inside Microsoft.Xrm.Client namespace.

Step 2
As per Figure 2, once we get the credentials, we just need to pass it to IOrganizationService to get all the entities of a particular organization. We can use the following code:

To get the entity attributes, we can use the Attribute Meta data class as shown below. Once we get all the attribute & its types, we can create a dynamic table inside the database with the help of this class.

Step 3
To get SQL server name and databases, we can use SQL server management object(SMO).

NOTE: Add following references to use the SMO:

To get instances of all available SQL Servers, use the following code:

As per figure 2, on selection of the server, we need to get a list of databases which are present on that particular server. For the same, use the following code as shown below:

Step 4
We are ready with our Entities, Entity attributes, Server name and Database. Now we need to create a table for the selected entity dynamically. For that purpose, we need to pass selected server and database name to SMO server and database class respectively. Then we need to create an object of table class, pass column name and data type to the table object and finally call the Create method of table object.

NOTE: Since MS Dynamics CRM attribute data types & SQL data types are different, we must replace CRM data type with SQL data type.

Step 5
Finally we are ready with our dynamic table. Now, we need to insert bulk of record into the table. To import data into CRM, we need to make a request to ExecuteMultipleRequest class and send the list of records to it.

I have used the following code for the same:

And we are done! I ended up creating a reusable component, which provides enough capability to import data from SQL to Dynamics CRM. My migration team loves me to the core!




Written by Neethuanna Matthew,  Microsoft Dynamics CRM Champion at Eternus Solutions