Known issues
There are some known issues which you should be aware of when you start using Data Import Export Framework or when you encounter some unexpected errors.
Start troubleshooting
When running the tool, you might notice some issues when data is incorrect. Initially you don’t know if errors are related to the source file or due to a setting or bug. How would you find out what is wrong? I will try to help you with the next notes. First of all, there can be errors at several stages.
- Staging status
- Target status
To be able to determine at what stage the processing stopped, you can view the status in the form Processing history.
Staging errors
For the reproduction of this error I created a small source file for Inventory opening transactions with the next lines.
When you look carefully at the contents, you may find already some incorrect and inconsistent values, but assume you have a file with thousands of lines, you will not check the lines yourself, wouldn’t you?
When there is an error on the Staging status you would expect to view some errors in the Error log. It could be the case that this error log does not have details to be able to see what is wrong with the import. For example, the option View error file is disabled and Staging log details does not provide any details.
What would be needed to do to have details available? The answer can be found in a parameter and an import option.
On the Data import/export framework parameters you can enable the option Create error file. This option will be used as default when you start a new import.
The option Create error file is an additional option which is defaulted from the parameter setting. You can also change the setting on this step. When it is enabled, the details are captured and stored in Microsoft Dynamics AX. Note that this will have a performance penalty and would be useful for troubleshooting only. When running the import where the Create error file option is enabled, the View error file option is enabled.
When clicking this button, AX will open a file which has only the lines that have an issue.
In many cases you will not be able to note on which line which field(s) would have caused the error. So how can we find out about this?
Together with the generation of the error file, also detailed information has been captured. When you now open the Staging log details form, you will get the information about violations.
Now we know that there are issues with values in the QTY and TRANSDATE columns. On purpose I used the comma instead of a dot for the decimal separator on one line. Also a month ’13’ does not exists. The date format depends on the Language locale field which can be setup on the Source data format.
If you have issues where the file looks fine, but no data is imported, you should check the Code page and Unicode settings on the Source data format. The source file might have another code page compared to the one that was setup in AX.
Target errors
When the staging data has been loaded successfully, you can view the staging records and validate the result manually and also use the validation to see if there would be any knows errors in upfront. The above used file has been modified to correct the data type violations, but the item numbers has been edited to have items in the file that does not exist in the Microsoft Dynamics AX demo company.
Using the Validate all option, no errors were reported. If there were errors, you can import a new file or correct the data in the staging details.
As the validation is successful, the target step was executed. In this case I got an error. You can then go to the execution history to read the error details. In this example I got the next log.
Using the Infolog, you can view all the details. In this case the Item ID is filled in the staging with a value that does not exists, so the data is not copied. Upon saving the record, the journal line is validated and raises this error. The error would be solved using the correct item numbers.
There is more
When using Data Import Export Framework, sometimes you can get some other errors. This post is intended to get you familiar with the data troubleshooting. Did you also get an error “Conversion failed when converting the nvarchar value ‘XXXX’ to data type int” on a custom built entity?
If you want to learn about the cause and how to solve this, watch my next post coming. Make sure you subscribe if you don`t want to miss it!
When Microsoft designed the Data Import Export Framework in AX2012 it provided some entities out of the box. In many scenarios you will be missing an entity. You can create your own entities in the development environment from scratch or you can use the wizard which will create the basic objects required for your new entity. Sometimes you might run into errors and you should start troubleshooting. This post will provide a walkthrough how to create an entity. When finished this entity has an error due to conversion of an enumeration field. A solution for this problem is also provided at the end of this blog.
Create a new entity
Suppose you are in the need for a new entity based on the Inventory posting setup. The next steps should be taken to create a new one based on the table InventPosting.
- Start Data Import Export Framework > Common > Create a custom entity for data import/export.
- Fill the value InventPosting in the Table name field or select this table using the lookup. Then click Next.
- Specify the InventPosting value in the field Display menu item name. This is the menu item that will be used when you want to drill down to the target data from e.g. the staging view. Click Next.
- Select the fields which should be supported within the new entity. Continue and finish the wizard.
- During the creation of the entity you might be asked to create relations on the new staging table. Always answer this question with Yes. If you choose No, an important relation to the target table might be missing, which could cause the execution only able to insert records and not update existing records.
- During the process you might also see database synchronization starts. Don’t abort this process. It could lead to wrong string lengths in the DMF tables which holds the field mappings.
The wizard created a new private project with all minimum required objects for the entity. For reference fields based on record-ID references, fields of a string type are created in the staging table. To be able to map the correct value, generateXXXXX methods are created to be able to handle the conversion.
- In this example the generateLedgerDimension method has been implemented fully with the correct coding. This might not be the case in every version of Data Import Export Framework in AX 2012. Compile the full project to see possible errors or open tasks.
- It appears that the method generateCategoryRelation has not been filled with the required coding. It has an //TODO section stating you have to implement the correct coding.
- Next to the coding, you also need to implement the DMFTargetTransFieldListAttribute correctly. This will provide knowledge to the entity which field(s) are used as input to find a record ID of the referenced table. The way to specify the fields are different in AX 2012 R3 and AX 2012 R2. Have a look at my blog post Change in data import export framework where this has been explained.
The complete method might look like the next screenshot when you have completed the task.
- In the previous method, the fields for input are defined, also the return field must be specified in the getReturnFields method. As there is no //TODO section in this method created by the wizard, you might overlook this part, causing the outcome of this method not linked automatically to the target field. So add the coding for the return field for the Category relation.
- Compile the project, synchronize the tables and run an Incremental or full CIL compilation.
The entity is now ready to be setup in the Target entities form and use it in a Processing group.
Conversion error
As told in the introduction, this entity will raise errors. This is at the time of copying data to the target. What is the exact error? What causes it? How to solve this? This will be explained below.
For the test I did create a very small CSV file with some records that could be used in the demonstration company USMF.
The source to staging was executed without problems. Note that the correct string values for the
Account type are inserted in the staging table.
When you want to execute the
Copy data to Target step, the job fails. The next error will be visible.
The error is stating that a nvarchar (string) field is not possible to convert to and int (integer). This is related to the enumeration fields in this table, for sure. But I learned that the enumeration conversion is working with the label, enumeration text and value number, so why is this failing?
SQL statement: SELECT T1.ITEMRELATION,T1.CUSTVENDRELATION,T1.TAXGROUPID,T1.INVENTACCOUNTTYPE,T1.ITEMCODE,T1.CUSTVENDCODE,T1.COSTCODE,T1.COSTRELATION,T1.CATEGORYRELATION,T1.LEDGERDIMENSION,T1.INVENTPROFILETYPEALL_RU,T1.INVENTPROFILETYPE_RU,T1.INVENTPROFILEID_RU,T1.SITECODE_CN,T1.SITERELATION_CN,T1.RECVERSION,T1.PARTITION,T1.RECID,T2.COSTRELATION,T2.CUSTVENDRELATION,T2.INVENTPROFILEID_RU,T2.ITEMRELATION,T2.SITERELATION_CN,T2.TAXGROUPID,T2.DEFINITIONGROUP,T2.ISSELECTED,T2.TRANSFERSTATUS,T2.EXECUTIONID,T2.ECORESCATEGORY_NAME,T2.ECORESCATEGORYHIERARCHY_NAME,T2.COSTCODE,T2.CUSTVENDCODE,T2.INVENTACCOUNTTYPE,T2.INVENTPROFILETYPE_RU,T2.INVENTPROFILETYPEALL_RU,T2.ITEMCODE,T2.LEDGERDIMENSION,T2.SITECODE_CN,T2.COSTGROUPID,T2.RECVERSION,T2.PARTITION,T2.RECID FROM INVENTPOSTING T1 CROSS JOIN DMFINVENTPOSTINGENTITY T2 WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID=?)) AND ((T2.PARTITION=?) AND ((((((((((((((T2.RECID=?) AND (T1.SITERELATION_CN=T2.SITERELATION_CN)) AND (T1.SITECODE_CN=T2.SITECODE_CN)) AND (T1.INVENTPROFILEID_RU=T2.INVENTPROFILEID_RU)) AND (T1.INVENTPROFILETYPE_RU=T2.INVENTPROFILETYPE_RU)) AND (T1.INVENTPROFILETYPEALL_RU=T2.INVENTPROFILETYPEALL_RU)) AND (T1.COSTRELATION=T2.COSTRELATION)) AND (T1.COSTCODE=T2.COSTCODE)) AND (T1.TAXGROUPID=T2.TAXGROUPID)) AND (T1.CUSTVENDRELATION=T2.CUSTVENDRELATION)) AND (T1.CUSTVENDCODE=T2.CUSTVENDCODE)) AND (T1.ITEMRELATION=T2.ITEMRELATION)) AND (T1.ITEMCODE=T2.ITEMCODE)) AND (T1.INVENTACCOUNTTYPE=T2.INVENTACCOUNTTYPE))) ORDER BY T1.INVENTACCOUNTTYPE,T1.CUSTVENDCODE,T1.CUSTVENDRELATION,T1.ITEMCODE,T1.ITEMRELATION,T1.TAXGROUPID,T1.INVENTPROFILETYPEALL_RU,T1.INVENTPROFILETYPE_RU,T1.INVENTPROFILEID_RU
After debugging and looking at the SQL statement it is noticed that it is not caused by the conversion from the staging to the target value for enumeration fields, but the attempt to find an existing record. AX tries to join the target and staging table record in a query to find a possible record to update instead of creating a new one. This join is build based on the InventPosting relation on the staging table. Below you will see the fields marked which are incorrect. Why?
This relation is automatically created by the wizard based on the primary index of the target table. It used the replacement key index in case of a record ID index and if this replacement key is unique.
But now the 64000 dollar question: How to solve it?
It is good to know that there are two attempts on finding an existing record. The first attempt is a query which is code based created where the staging and target table are linked using the table relation to the Target table. Just removing this relation will not solve the problem. It will then think there is no relation, so only records will be inserted at any time. The target table will then raise duplicate key errors.
Removing the incorrect fields from the relation is also not a good idea. It will then find the wrong existing records and will cause updating these existing records in stead of creating new records. This is the case when e.g. the values for Item and Customer relation are the same, but the only difference is in the Account type selection.
So now we have to know how and when the second attempt is executed and how this works. If the first attempt will not find any existing record, it will then find a record in the target table based on the replacement key of the target table. If there is no replacement key, it will try find an existing record based on primary index if this is not containing the Record ID field.
So we have to cheat AX to have no record found in the first attempt. For that, we need to delete all fields relations and create an impossible relation. E.g. a record ID from the staging table will be linked to the relation type field on the target table. Record IDs usually start with high numbers, so it will never find an existing record with low relation type values. In this way the first query method will have no existing record found and the second attempt is working correctly for this table.
If you make the changes and saves, compiles the table, you can rerun the target execution step which will now give the correct outcome without error.
Before you implement a similar cheat on your entities, make sure you test it carefully in a separate environment to make sure it will work correctly.
The setup of processing groups in AX is the base for SSIS packages which will pick up the source records and will put it with our without some basic transformations in the staging table. SSIS is very efficient and really a winner on performance for this type of tasks.
