Consider a scenario where you are importing a lot of data using DIXF, but one or more of the rows in the input file contain “bad data” that result in some rows not being imported OK into your staging table. How do you establish which rows are the problem, so that you can correct them?
In the example below, I am working with the Units demo data file (CSV – delimited) that ships with AX 2012 R3.
You can usually find this file here:
C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\DemoFiles\Delimited
In this example, I have copied the UnitOfMeasureEntity.txt file to another folder called C:\DIXF_IMPORT , renamed the file and called it ‘MSFT_UnitOfMeasureEntity.txt‘ and I’ve edited one of the 6 rows in it so that it will generate an error.
The file I am working with looks like this:
Please note that the file header column info (Symbol, DecimalPrecision, etc.) has been truncated – Check the real sample data file for the details
DIXF_AD,—,No,No,United States customary units,Mass,Days,Pounds
DIXF_AE,0,No,No,United States customary units,Mass,Seconds,Pint
DIXF_AF,1,No,No,United States customary units,Undefined,Metric tons,1/8 square inch
The change I’ve made is highlighted in red above.
1. The error
When I try to import the Units entity data file shown above into staging, I get the following error:
Warning Message (17:46:01) The file for entity Units is having 1 error(s)
Info Message (17:46:01) ‘0’ ‘Units’ record(s) inserted in staging
Warning Message (17:46:01) For additional warnings or errors, please check the log
2. How to find the relevant rows in the input file that need to be fixed
There are at least two ways to establish what rows in your input file are causing problems.
Data import/export framework parameters / Create error file
Navigate to /Data import export framework/Setup , click on ‘Data import/export framework parameters‘, and check the ‘Create error file’ checkbox.
Once you’ve done this, you can re-run your ‘Get staging data’ process and any errors will be captured in an error file for review.
Staging data execution (DMFStagingData form) / Details / Create error file
If you haven’t enabled this functionality using the method described above, and you want to enable it for a particular Processing group that you are importing into staging, you can also do that.
Navigate to /Data import export framework/Common, click on ‘Processing group’, select the relevant ‘Processing group’ (in this case it is Units) and click on ‘Get staging data’.
When the subsequent screen appears, click on the ‘Details’ link and check the ‘Create error file’ checkbox, and finally, on ‘Run’.
Again, any errors will be captured in an error file for review.
You can review the error file by looking at the ‘Execution history’ of your ‘Processing group’, then clicking on ‘Error log’ followed by ‘View error file’.
3. Get staging data again once the errors in your input file have been corrected
Once I put a valid DecimalPrecision value in my input file and re-run the ‘Get staging data’ processing, the 6 rows in my input file are imported into staging OK.
InfoLog shows successful import.
Data visible OK in the staging table in the SQL Server database.