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:
- Export & import with MS Dynamics CRM
- Creating a dynamic table in our local database
Let’s take a look at the screenshots:
name and databases of selected server and a button which will create a dynamic table on selected database
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:
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 SQL server name and databases, we can use SQL server management object(SMO).NOTE: Add following references to use the SMO:
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:
Related content
Enterprise Application
We work closely with our clients, understand their vision and design, and architect, develop, and manage their digital products.
SLA-based Managed Services
We proactively monitor endpoints and keep systems up-to-date, preventing issues and ensuring optimal performance.
Performance Management & Monitoring
We improve product performance by enhancing features, optimizing platforms and polishing the design & architecture.
Stay up to date with InfoBeans
Insights from our team of experts who deliver digital software day in and day out.
Thank you for signing up!
Thanks a lot for your interest. You are already a subscriber. You will receive your first newsletter soon.
