Microsoft Dynamics AX 2012 DIXF – Composite Entity Primer (XML format) – Part2
Purpose: In my previous article I illustrated how to use Dynamics AX 2012 DIXF for import of custom data, in particular I focused on Entity type = Composite Entity using XML file format. The purpose of this article is to discuss what may go wrong when you setting up Composite Entities using XML file format and what you need to do to overcome those issues.
Issues and Solutions
Please note Data Import Export Framework has a number of architectural components and one of them is Microsoft Dynamics AX Data Import/Export Framework Service
Services
DataImportExportFramework folder
There are number of DLLs supporting Microsoft Dynamics AX Data Import/Export Framework Service
From troubleshooting perspective this means that the code may be executed not only in context of Microsoft Dynamics AX 2012 AOS process but also in context of Microsoft Dynamics AX Data Import/Export Framework Service process
From data perspective the obvious prerequisite would be to have appropriate XML data included in the source file under respective Entity.XMLDocumentRowElement (for example, <AlexTableEntity>…</AlexTableEntity> or <AlexLineEntity>…</AlexLineEntity>). In case you properly included the data in the file let's discuss what other nuances you may come across
1.Relations
Please note in order to properly link Composite entity entities you need to introduce a special XML element/attribute which name consists of your header entity XMLDocumentRowElement + "_Id"
So let's explore what happens if we don't do so and change the file as described below
AlexXMLElement file
AlexXMLElement file (text)
<?xml version="1.0" encoding="utf-8"?> <Document><AlexTableEntity><FieldA>A1</FieldA><FieldB>B1</FieldB><ID>1</ID><RowId>1</RowId></AlexTableEntity> <AlexLineEntity><FieldC>C1</FieldC><FieldD>D1</FieldD><ID>1</ID><RowId>1</RowId></AlexLineEntity></Document> |
After we changed the file for the cleanness of the experiment I'll regenerate mapping for Composite entity and try to Preview the contents of the header or line
Preview AlexTable or AlexLine
Error
Error (text)
ExecuteReader: CommandText property has not been initialized For additional warnings or errors, please check the log |
Event
Event (text)
ExecuteReader: CommandText property has not been initialized at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFCompositeEntity.ShowPreview() at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.Service.ServiceHelper.ShowPreviewHeaderLine(DMFCompositeEntity dmfCompEnt) |
You may face the same issue when using Attribute-based XML format
AlexXMLAttribute file
AlexXMLAttribute file (text)
<?xml version="1.0" encoding="utf-8"?> <AlexTableEntity> <AlexTableEntity FieldA="A2" FieldB="B2" ID="1" RowId="1" /> <AlexLineEntity FieldC="C2" FieldD="D2" ID="1" RowID="1" /> </AlexTableEntity> |
Again, in order to resolve this issue please include a special XML element/attribute which name consists of your header entity XMLDocumentRowElement + "_Id" to link derived records with parent records
2.Not all fields mapped in Staging
Depending on what you have in the file and how you compose it you may end up in the situation when not all the fields are mapped (Source -> Staging)
For example, if you have a file as below where you included <AlexLineEntity> data into <AlexTableEntity> data
AlexXMLElement file
AlexXMLElement file (text)
<?xml version="1.0" encoding="utf-8"?> <Document><AlexTableEntity><AlexTableEntity_Id>1</AlexTableEntity_Id><FieldA>A1</FieldA><FieldB>B1</FieldB><ID>1</ID><RowId>1</RowId><AlexLineEntity><AlexTableEntity_Id>1</AlexTableEntity_Id><FieldC>C1</FieldC><FieldD>D1</FieldD><ID>1</ID><RowId>1</RowId></AlexLineEntity></AlexTableEntity> </Document> |
At the time when you regenerate Source to Staging mapping you may notice a message like below about "There are field(s) which are not mapped to Entity"
Infolog
Reviewing the mapping you may see the following
Mapping (Visualization)
Mapping (Details)
Should this happen you may see an error when trying to Preview data
Preview source file
Error
Error (text)
There are fields in file which are not mapped to entity field. Exception from HRESULT: 0xC0010009 For additional warnings or errors, please check the log |
Event
Event (text)
Exception from HRESULT: 0xC0010009 at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFCompositeEntity.ShowPreview() at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.Service.ServiceHelper.ShowPreviewHeaderLine(DMFCompositeEntity dmfCompEnt) |
Of course, you can easily avoid it by properly structuring your source file
3.Not all mapped fields specified in the file
Another situation is when you have everything properly mapped but not all mapped fields are actually specified in the source file
Mapping
AlexXMLElement file
AlexXMLElement file (text)
<?xml version="1.0" encoding="utf-8"?> <Document><AlexTableEntity><AlexTableEntity_Id>1</AlexTableEntity_Id><FieldA>A1</FieldA><FieldB>B1</FieldB><ID>1</ID><RowId>1</RowId></AlexTableEntity> <AlexLineEntity><AlexTableEntity_Id>1</AlexTableEntity_Id><FieldC>C1</FieldC><ID>1</ID><RowId>1</RowId></AlexLineEntity></Document> |
For example, in this case I forgot to specify FieldD value
After that when you try to preview the data
Preview source file
You may see the following error
Error
Error (text)
Exception from HRESULT: 0xC0010009 For additional warnings or errors, please check the log |
Event
Event (text)
Exception from HRESULT: 0xC0010009 at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFCompositeEntity.ShowPreview() at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.Service.ServiceHelper.ShowPreviewHeaderLine(DMFCompositeEntity dmfCompEnt) |
4.Type mismatch
Previous 3 scenarios were errors which may occur at the time of SSIS package creation
In fact you may come across issues occurring at the time of SSIS package execution as well. The most typical issue is type mismatch. For example, you specify "_Id" field as string but it has to be numeric
AlexXMLElement file
AlexXMLElement file (text)
<?xml version="1.0" encoding="utf-8"?> <Document><AlexTableEntity><AlexTableEntity_Id>A</AlexTableEntity_Id><FieldA>A1</FieldA><FieldB>B1</FieldB><ID>1</ID><RowId>1</RowId></AlexTableEntity><AlexLineEntity><AlexTableEntity_Id>A</AlexTableEntity_Id><FieldC>C1</FieldC><FieldD>D1</FieldD><ID>1</ID><RowId>1</RowId></AlexLineEntity></Document> |
Now when you try to preview the data
Preview source file
You may see the following error
Error
Error (text)
Package execution failed please check event log in DMF service box For additional warnings or errors, please check the log |
Event
Event (text)
<?xml version="1.0"?><Errors><Error><ErrorCode>-1071636471</ErrorCode><SubComponent>AlexTable,OLE DB Error [341]</SubComponent><Description>SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.". </Description></Error><Error><ErrorCode>-1071636471</ErrorCode><SubComponent>AlexLine,OLE DB Error [629]</SubComponent><Description>SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.". </Description></Error><Error><ErrorCode>-1071607780</ErrorCode><SubComponent>AlexLine,OLE DB Error [629]</SubComponent><Description>There was an error with AlexLine,OLE DB Error.Inputs[OLE DB Destination Input].Columns[AlexTableEntity_Id] on AlexLine,OLE DB Error.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.". </Description></Error><Error><ErrorCode>-1071607780</ErrorCode><SubComponent>AlexTable,OLE DB Error [341]</SubComponent><Description>There was an error with AlexTable,OLE DB Error.Inputs[OLE DB Destination Input].Columns[AlexTableEntity_Id] on AlexTable,OLE DB Error.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.". </Description></Error><Error><ErrorCode>-1071607767</ErrorCode><SubComponent>AlexTable,OLE DB Error [341]</SubComponent><Description>SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "AlexTable,OLE DB Error.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "AlexTable,OLE DB Error.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. </Description></Error><Error><ErrorCode>-1071607767</ErrorCode><SubComponent>AlexLine,OLE DB Error [629]</SubComponent><Description>SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "AlexLine,OLE DB Error.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "AlexLine,OLE DB Error.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. </Description></Error><Error><ErrorCode>-1073450974</ErrorCode><SubComponent>SSIS.Pipeline</SubComponent><Description>SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "AlexTable,OLE DB Error" (341) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (354). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. </Description></Error><Error><ErrorCode>-1073450974</ErrorCode><SubComponent>SSIS.Pipeline</SubComponent><Description>SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "AlexLine,OLE DB Error" (629) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (642). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. </Description></Error></Errors> |
Event
Event (text)
DMF043 at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelper.DMFCompositeEntity.ShowPreview() at Microsoft.Dynamics.AX.Framework.Tools.DMF.SSISHelperService.Service.ServiceHelper.ShowPreviewHeaderLine(DMFCompositeEntity dmfCompEnt) |
Author: Alex Anikiiev, PhD, MCP
Tags: Dynamics ERP, Dynamics AX 2012, DIXF, Data Import Export Framework, Data Import, Data Conversion, Data Migration, Composite Entity, XML, Attribute, Element
Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.