In most, Data Migration and Integration Projects, retrieving GUIDs of Dynamics CRM records is always challenging. The fields that are of lookup type need the GUIDs of the records to load. In this tutorial, we’ll learn how to retrieve GUIDs of CRM records and load them in an SQL table, which can further be used for Data Migration and Integration tasks. We will take the logical name of the entity and the logical name (primarily name) of any of its fields as input in our C # console application and dump the GUID of all its records in the SQL table.
- A valid Dynamics-365 CRM Instance
- Microsoft SQL Server Management Studio
- An SQL Server & a DB with valid credentials
- MS Visual Studio (version 2017 or above)
Steps to load Microsoft Dynamics 365 CRM (CE) Records GUIDs to an SQL DB Table:
1. Open MS SQL Server Management Studio and create a table to store the GUIDs. Here we have created a table CE_RecordGuids_Sample” with the columns Entity, Name, Guide, and ReferenceField (lookup).
2. Open Visual Studio and create a new C# ConsoleApp where we will establish a connection with the CE instance first, retrieve the GUIDs after getting input from the user on the Console, establish the connection with SQL Server, and DB and then dump the GUIDs in the table CE_RecordGuids_Sample.
3. Go to View → Server Explorer → Data Connections → Add Connection. Select Microsoft SQL Server and then enter your Server Name. Then choose your SQL DB.
You can verify the connection by clicking the ‘Test Connection’ button.
4. Go under Data Connection in Server Explorer and right-click on your newly added DB connection.
Select Properties and Copy the Connection String. Keep it somewhere handy as we’ll need it in our code.
5. Make sure you add the XRM and SQL DLLs as Assembly References to your project.
6. Write a C# function to establish a connection to your CRM instance. It will take the Soap URL of your Dynamics CRM Company, username, and password as parameters. It will return Boolean values (true/false) based on whether the connection is established or not.
For SOAP Service URL, go to Settings → Customizations → Developer Resources and Copy the URL under Organization Service.
7. Let’s code our Main Function. We’ll have to call the ConnectToCE function that we defined above. We’ll pass our Organization SOAP service URL, username, and password.
8. Once the connection to the CE instance is established, accept the Entity’s logical name and its field’s logical name from the user and retrieve records from CRM.
9. Establish the connection to your SQL DB and insert records to the table CE_RecordGuids_Sample. Before inserting, check if there are already any records for the same entity in the table. If there are, delete them and then insert in the new ones.
So basically, it’s an UPSERT (INSERT + UPDATE) operation.
10. Let’s run our program now. We will input ‘account’ for the entity’s logical name and ‘name’ for the field’s logical name.
Output on console
The Program has executed successfully.
138 Account records are present in our CRM instance.
12. GUIDs of all of them are successfully loaded in our SQL Table.
Following is the complete C# Code. Provide your values for Username, Password, SOAP Organization URL, and SQL DB Connection String.
With the above comprehensive tutorial, we have found a way to store the GUID values of CRM Entity Records in an SQL Database with UPSERT operation. With this major step, we can perform multiple complex Data Migration & Integration tasks by essentially resolving conflicts around lookup fields.