Importing data into Dynamics 365 or Dynamics CRM successfully on your first attempt can be as predictable as the flip of a coin. Sometimes lady luck rolls your way and you feel like you won the lottery. Other times, you feel like the system is throwing a tantrum and will never take your data. I have encountered both scenarios in my experience with importing data.
One error I encountered while working with a colleague was the Generic SQL Error.
This error is the enigma of errors when working in CRM. It can be more puzzling when you encounter it during data import. I did some research to see what others may have done to resolve the issue when then experienced it. Some posts mentioned looking at the data type of the field in CRM and make sure the data in your import file matches it. I broke down the import to only import one record so I could better troubleshoot. No luck. I started to cut empty columns from the file with no success.
Okay…I will end the suspense. The column that was having issues was a whole number field. I knew that there were no workflows that were running that would cause the issue. The import should succeed and the workflow jobs would fail even if there had been workflows associated with the update or create of the record.
Business Rules. One of my favorite features of Dynamics CRM. Alright, I have a lot of favorite features, but Business Rules are pretty cool. Looking at the field properties, I saw there was a Business Rule with the scope of Entity activated. The rule was configured to run when this field contained data. Here is an example of the rule where the field I was updating during import is called CURRENT:
Condition: If CURRENT contains data
Action: Set CAPACITY to CURRENT / TOTAL CAPACITY
What was happening was CURRENT had a whole number as was expected. The problem was that TOTAL CAPACITY was NULL. Even if CURRENT had a whole number like 5, I was going to run into a problem with trying to take 5 / NULL. How did I not see this error when creating a new record through the form? During the load of the form, these fields would be populated with zeros as the default values.
Since the rule had a scope of Entity, it was running on the create of the record and the fields don’t populate with zeros because I did not include them in the import file. So what could I do? There were two options for me:
- Update the data file to include all the fields included in any Entity Scope business rules and update them all to zero.
- Deactivate the Business Rule just for Import
In the end I chose to deactivate the Business Rule as it required the least amount of modification. The import file contained a few thousand records and while I could quickly populate that information in Excel, I didn’t want to make the assumption that those fields should be populated with zero. After the records were created successfully, I reactivated the Business Rule.
I hope this experience helps. Have you run into a strange error message while importing records in Dynamics CRM? Please let us know in the comments.