Common Data Import Errors

If you have ever imported data using Data management in Microsoft Dynamics 365 for Finance and Operations and received an error before any records even went in, I am sure you were frustrated. 

Here are a few tips and tricks to consider before ever importing your data:

• Ensure the data formatting in Excel is consistent
• Custom or Special data formats in Excel will not import properly, use standard formatting always
• Data that was derived from formulas will likely generate an error message. Copy that data and paste it back in as values instead of a formula. 
• Check spellings - especially on data that is validated by the system (Address/city names, Dropdown values, etc)

Once your data is clean, you can still get errors. Here are some common errors that you can run into before any records ever hit the staging table. 

Data Value Violates Integrity Constraints

You may see an error that says, "The data value violates integrity constraints" followed by some obscene number of records, or not. 

Your Excel file has blank lines that you cannot see causing the issue. This is usually from the data being pasted in as a column from a legacy system export and including rows and rows of blank values. 

Solutions:

1. Delete the blank lines (easier said than done)
2. Copy and paste ONLY your good data into a new Excel file (easiest solution)

Duplicate Records Must Be Removed From the File Prior To Import

Okay, you look through your data and there are no duplicate records. Nothing is in the Staging table, so there aren't any duplicates there either. What is going on here? 

I recently saw these exact errors:

Failed to insert record into staging table. The keys of the record are DEFINITIONGROUP, EXECUTIONID, PARTITION, VENDORACCOUNTNUMBER. Duplicate records must be removed from the file prior to import.

Failed to insert record into staging table. The keys of the record are CUSTOMERACCOUNT, DEFINITIONGROUP, EXECUTIONID, PARTITION. Duplicate records must be removed from the file prior to import.

You may have seen any number of variations on this record, but if you are trying to get the numbers to populate using a number sequence you have defined, you need to change your mapping on the import. This error occurs because D365FO sees all of the blanks as duplicate item numbers. Yup. This is a real thing. 

When you bring in the file, it will automatically map the Source field (your import file) to the Staging field (D365FO field). However, it doesn't take into account that you are expecting it to automatically populate the Vendor account number (in my example - could be a Customer account number, any kind of number sequence). Therefore, in the mapping, you need to check "Auto-generated" and it will change the Source field to Auto. 

Sample Vendor mapping with Source field set to Auto

Sample Customer mapping with Source field set to Auto

Data in Numeric Format Needs to be Text

You may see this error in your Execution log: An excel column containing strings was set as a numeric column by SSIS. If any column data is missing in staging, re-import after setting the cell format to Text in Excel for column PERSONNELNUMBER,PRIMARYCONTACTPHONEEXTENSION

This is due to the file needing the values to be set as Text fields in your Excel file. As I stated in the first part of this post, the formatting in Excel needs to be consistent!

Epilogue

I hope this blog post was helpful. These are errors I tend to come across repeatedly - so hopefully putting these into a blog will help others who see these errors. What specific errors do you see frequently? Leave a note in the comments!