In Dynamics 365 Finance and Operations data migration errors can be the most frustrating types of errors because they can be the most cryptic and unhelpful. After years of doing data migration, and spinning my wheels on some of these errors, I have compile some of the most common data migration errors and the resolutions to them. My hope is that this will save you time if you encounter these same errors during your own data migration.
Data Migration Errors and Resolutions
Below are some of the most common data migration errors and what I did to resolve them. They are broken down by data entity but some of the same causes may be applicable to other data entities.
Data Entity: Sales order lines V2
Error: Failed to insert record into staging table. The keys of the record are DEFINITIONGROUP, EXECUTIONID, INVENTORYLOTID, PARTITION. Duplicate records must be removed from the file prior to import.-The duplicate key value is
Resolution: You will get this error if you simply upload a sales order lines V2 file and click the “Import” button. The reason you get this error is because the INVENTORYLOTID field needs to be generated by the system, so you need to enable the “Auto-generated” flag “Map to source staging” form for the INVENTORYLOTID field. To do this click the “View map” icon for the Sales order lines V2 data entity.
Next click the “Mapping details” tab.
Now find the INVENTORYLOTID field and click the checkmark in the “Auto-generated” column. This will have the system autogenerate this identifier and allow the import to occur.
Data Entity: Released Products V2
Error: Results. Invalid argument.
Resolution: If you have a product in your Released Products V2 file that does not exist in your Products V2 you will get this very unhelpful error. To fix this you need to also include the product in your Products V2 file. This will create a record in EcoResProduct table which is required for a product to be released.
Data Entity: Released Products V2
Error: File contains duplicate rows, cannot insert data into staging due to unique key violation.-
Resolution: In my case I had products in the file that were longer than 20 characters long which is the string size limit for the “ITEMID” field. The system was truncating items after 20 characters and this was creating duplicate ITEMID values. The resolution is to either change the item ID to be less than 20 characters or you can extend the extended data type for ITEMID to be longer than 20 characters.
Data Entity: Released Products V2
Error: You cannot change the item model group because that would cause the Inventory policy to be changed.
Resolution: In this case I tried to reload items and change the item model group from a stocked model group to non-stocked. In order to do this, I first needed to delete all products and then reload the file.
Data Entity: Bill of materials lines V3
Error: The file for entity Bill of materials lines V3 has 1045099 error(s).
Resolution: The person loading the file had accidentally copied down values in one field to the end of the spreadsheet so when D365 was scanning the file to load into the staging table it noticed that there were over one million records that did not have required fields populated. If you get an error like this make sure you check the end of the file and make sure there are not any one off columns that were accidentally copied to the end of the spreadsheet.
Data Entity: Bill of materials lines V3
Error: 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 BOMID.
Resolution: Sometimes D365 Finance and Operations data management framework can be a little sensitive to fields that are not text type fields in Excel. To fix this issue I simply created a new tab in the spreadsheet and changed all the cells to text cells. Then I just copied all the data from the sheet with the errors and pasted them as values into the other tab with the text cells. I reloaded the file, and this error was resolved.
Data Entity: Open purchase price journal lines
Error: Failed to insert record into staging table. The keys of the record are DEFINITIONGROUP, EXECUTIONID, LINENUMBER, PARTITION, TRADEAGREEMENTJOURNALNUMBER. Duplicate records must be removed from the file prior to import.-The duplicate key value is ‘0’ ‘Open purchase price journal lines’ record(s) inserted in staging.
Resolution: The LINENUMBER field has to be sequential numbers. So, if there are duplicate line numbers you will get this error when trying to load trade agreements into a journal. The solution is to renumber the LINENUMBER field so that it is sequential all the way to the end of the file (1,2,3 etc.).
Data Entity: Product global trade item number assignments V2
Error: The file for entity Product global trade item number assignments V2 has 1 error(s) Parallel tasks configured for Product global trade item number assignments V2: 0, Staging records: 0 ‘0’ ‘Product global trade item number assignments V2’ record(s) inserted in staging.
Resolution: Make sure all values in the spreadsheet are all text values. Also make sure there are no duplicate GTIN codes.
Data Entity: External item descriptions for vendors V2
Error: Results. The Style has not been assigned to the product Results. validateWrite failed on data source ‘CustVendExternalItem (CustVendExternalItem)’
Resolution: This error occurs because the style dimension specified in the data has not yet been released. The solution is to release that style dimension and the data will load. You can use the Released product variants V2 to do this through data migration.
Takeaways
I hope these resolutions to some of the most common data migration errors I have experience over my career can help you to quickly resolve them when you encounter them yourself. If you have any other data migration error not listed in this post that you cannot figure out, please submit the error message to the contact us form and I will try to find a solution and post it here.