Tuomas Kesti

How to do bulk update to Dynamics 365 data without having unique keys?

/in /by

Have you ever had a situation where you would need to do one-time update of data in Dynamics CRM from some data source but the data source is in format that the data import wizard in Dynamics CRM is not an option? Well, I have run into this situation several times and previously, the solution into this challenge has been either to:

  • Do so called legendary excel hazzle; in other words, export data first from CRM into excel (to have the necessary metadata), then do VLOOKUP comparison between the other data source (exported also to excel) and fetch the data from that data source to the excel which contains metadata exported from CRM. And then finally use the CRM data import wizard to update the data.
  • Develop a custom tool using CRM SDK to do the data update. This is so called BYOB tactic.
  • Use a third party (paid and licensed) tool to do the update

Some time ago I did a video blog regarding the KingswaySoft SSIS integration toolkit. In that blog post, I focused on the data migration done from one Dynamics CRM instance to another.

Here in this blog post, I will tell you how to do bulk updates from an excel sheet into Dynamics 365 without having the CRM metadata from Dynamics 365 available in the excel. Naturally a common, unique key is needed between the data in the excel sheet and Dynamics 365 record but that does not necessarily need to be the CRM GUID as I show you below. The steps to do the update are quite simple.

1.     Prepare the SSIS project details

First thing you need to do is to create a new SSIS package and set the data source as excel file. A couple of nice-to-know tips about this:

  • You need to have 32bit Microsoft Access Database Engine installed on the machine where you are running the SSIS package. This is required to make the connectivity from SSIS package to the excel file.
  • You need to set the SSIS project property Run64BitRuntime to False:

2.     Configure the excel data source

The actual data source properties in the SSIS project look as following:

So here you will create a new connection using Excel Connection Manager, set the Data access mode (which is “Table or view” if you need all the data from the excel sheet) and then just select the data sheet from which you need the data.

 

3.     Configure the destination properties

The destination in this example is an on-premise Dynamics 365. I will not get into the connectivity details in this blog post which I have already explained in my previous blog post regarding this subject. Instead, the interesting details you would need to configure are the following:

So, you would need to select:

  • Update as an action to do
  • Select the destination entity in CRM
  • Most importantly, select that you will manually specify the update key to be used in the data update. Then later on when you configure the field mappings from the excel sheet to CRM entity fields, you will configure the update key to be used.

And that’s pretty much it. You are ready to go and run the SSIS package and update the data in CRM from the data in excel file.

To summarize, this tool makes your life much easier when you are working with Dynamics CRM projects and especially dealing with data management in CRM. I highly recommend taking a look at the tool, it has saved me lots of tedious moments working with different types of excel hazzles. Oh, what is the BYOB tactic? Keep on reading this blog and you will find out sooner or later.

Read more: