During a conversion where I used the Data import export framework (DIXF), I encountered an issue with the substitution of values. The conversions are used if the source file has other codes compared to the setup in your AX environment. This post will tell you how the conversion of values is performed and how to solve the issue I had.
What is it?
When your source file has e.g. country codes which do not match the codes used in AX2012, you can use the conversions in the source file mapping.
During import of the source file into the staging table, the values gets converted if there is a source value found in this table. Interesting to know is that SQL Server Integration Services (SSIS) is used to import the source into the staging tables. Also the conversion of values is done with use of the Data Import/Export Framework service component which uses SSIS techniques. The advantage to use SSIS is gaining high performance. So during the source to staging, in fact no business logic in Dynamics AX is used.
Issue and resolution
My issue was that not all source values were converted into the right staging value. Believe it or not, but a legacy system had only country names stored. So instead of having (ISO) codes of the countries a name was used. This seems to be no problem as it is possible to enter all the source values like you can see in the next screen shot.
When loading the staging data it appeared that the source values were converted, except for the source value ‘Netherlands’. The staging data contained the value ‘Netherland’. So the last character was lost. This appeared to be the case for all records with this country. The source file did have the correct values.
When there are only a few records with the wrong Country/region id’s it is possible to correct the staging values and continue pushing the records to the target. In this case there were too many errors and I started searching where in the AX logic this could be changed. It appeared to be handled in the DIXF service component which I already mentioned above. The cause is that the data type for this field is limited to 10 characters. The value ‘Netherlands’ has 11 characters and thus the string was truncated before it could convert it to the desired staging value. To solve this issue, it is possible to go to the table design of the staging table. In case of the Customer entity this is the table DMFCustomerEntity. The staging field CountryRegionId is using an extended data type which represents a string of 10 characters. You can remove the ExtendedDataType and adjust the StringSize property like the picture below.
A neater solution would be duplicating the existing Extended data type and change the String size on this duplicated type. Then you can reuse it on possible other DIXF entities. When you have changed the string size, you need to save and compile the table. Also an incremental CIL compilation is required to have it recognized. When this is done, you need to regenerate the source mapping to have it activated. After that it is able to convert strings up to 20 characters. When needed, you can make this kind of fields even larger.
There is more…
If you already had a quite comprehensive source mapping, then you can also change the string size of the source fields in a table directly. The source fields are stored in the table DMFDefinitionGroupEntityXMLFields. There are two fields related to the string size used by the DIXF service component: Max_Length and FieldSize. You can open the table browser and change the values directly. That will save you from regenerating the complete source mapping. You have to change the Maximum length prior to changing the field size itself. In the screen shot below you will see the field size which was created when the staging table still had a length of 10 characters. This can be changed to 20. Note that still the change on the staging table is required.
That’s all for now. Till next time!
if I change the fieldsize value in the DMFDefinitionGroupEntityXMLfields table along with Max_length field, will it not consider the increased value as the string size of the field in all the entities rather than changing the string size of the field in every entity? if so, please explain me why.
When you have a scenario like described in my blog, I assume it is related to one or two fields. In the scenario you change the data in the table DMFDefinitionGroupEntityXMLfields, this is data only. The DMF entity table is not automatically aware of this change or vice versa.
Can you explain why you are mentioning “in all the entities”? Do you have a data type used in multiple entities?
Hi Andre, when using the DMF for export to Excel for instance customer data, fields like credit limit are exported with 10 decimals. Do you happen to know if you can change this behavior in the DMFDefinitionGroupEntityXMLfields? The customer wants only a rounded two decimals value. What would be the appropriate way to achieve this?