Sending XML Files to eConnect

I have been working on an eConnect integration that needs to import up to 40,000 sales transactions a day into Dynamics GP 9.

The challenge is that the data is coming from an off-site eCommerce web site, so we had to export the data from the web site so that we could transfer the data for import to GP.

After considering the pros and cons of CSV and XML, we chose XML because we thought that it would be easier to use XML to handle the header, lines, taxes, and payments in one file rather than having to deal with multiple CSV files. In this respect, we were right--it is easier to have one XML file to process vs. four CSV files. But in other respects, most of which I didn't anticipate, sending XML files to eConnect has some challenges.

When the client offered to prepare and export the XML files for me in the eConnect format, I thought it was great, since it would save me time--I would only need to read the XML file and just pass it over to eConnect. Simple, right? Famous last words.

It turns out, that for this project, importing XML files was moderately complex and posed some challenges.

My first challenge was trying to validate the structure of the XML file, which the client was creating. Normally the XML would be created by the eConnect .NET Serialization assemblies, in which case you never have to worry about the physical format of the XML file. But when the XML is being created by another system, I thought it would make sense to try and validate the format before import.

I thought this would be pretty straightforward, since eConnect provides XSD files that you can use to verify your XML files. After hours of trying to get the XSD validation to work, and a few posts to the great gurus at Experts Exchange, I realized that the XSD validation process was surprisingly impractical and was useless for my project. The eConnect XSD files utilize a few options that make the validation so rigid that I was unable to use it. And more surprising, because of the way XSD validation is performed, the process only returns one error at a time. In XML files with 2,000 to 4,000 transactions each , validating and fixing the XML file format one error at a time was impossible. I ended up just sending the XML file to eConnect and using those more informative errors to get the XML file format correct.

Next, I had to be absolutely sure that eConnect would accept and process XML files with multiple transactions. Processing 40,000 transactions a day using individual transation files was completely impractical, so we really needed to be able to send at lesat several hundred transactions in each file. I thought it would be possible, but with eConnect, until I see it working, I don't make any assumptions. And I also didn't know how sending large files to eConnect would affect the performance of the integration. Fortunately, once we worked out all of the kinks in the XML format, I was able to send a single XML file with 2,000 transactions to eConnect and all of the invoices were created successfully. I have since been able to send a single SOP invoice file with 4,700 transactions and have it import successfully. (see caveat below)

And of course, there is data validation. When you send a single file with thousands of transactions to eConnect, a single data error in that file will cause the entire file import to fail. So it is critical to validate the file thoroughly before attempting to import it, checking most of the node values to confirm that they will not cause a failure. I am validating customer IDs, item numbers, item types, SOP types, transaction dates, document subtotals vs. line totals, and tax IDs. I also have to make sure that the items have valid price lists, item site records exist, and that the ship method is valid. Fortunately this is easy to do with XML and the validation is fairly fast.

And then came the dreaded eConnect "System Error" messages. I haven't run into it this error yet with eConnect 10, but it is definitely an issue in GP 9. A System Error is returned by eConnect for various reasons, but unfortunately eConnect doesn't provide any additional information, so you have to determine the cause through guessing and trial and error. The most common reason for the eConnect System Error is that an XML node is missing. In some cases, nodes are only required under certain circumstances, so although the eConnect help file says that the node is optional, it may actually be required for your particular transaction scenario. So after hours of testing and troubleshooting, we were finally able to identify the cause of the System Errors. In some cases, a node was missing, and in other cases, it was because some of the nodes were not in the exact order that eConnect exptected (XSD theoretically checks for this, but as I described, it just isn't practical).

So all is well and I'm able to process the large XML files on my development server. But when we deployed to the client's server, we received System Errors yet again. We are able to process small files (with 250 transactions) on the client server, but the file with 4,700 transactions fails. I guessed it was because of some slight configuration difference between the client's GP environment and mine, and that one or more transactions were causing the error.

So I wrote a new import routine that would take the single XML file and import each transaction individually. This way we could find the individual transactions that were causing the failure. Once we installed the new integration and tested it, to our surprise, all 4,700 individual transactions imported fine. Our only conclusion is that there must be something on the client's server that caused the large files to fail--a problem that I don't have on my development server. We don't yet know how many transactions we can include in a file, but we will be trying different file sizes to see what will work consistently.

Finally, while doing live imports, I discovered a new quirk of eConnect 9 that surprised me. After importing SOP invoices, we were importing SOP Payments (taCreateSopPaymentInsertRecord) from a separate file. My validation routine detected that there was one error in a file, but somehow the entire file imported successfully. A payment for an invoice that did not exist in GP was not rejected by eConnect. I then created a test payment XML file with a fake customer and fake invoice number. Sure enough, eConnect procesed it without returning any errors--but of course the payment record did not appear in GP. Clearly eConnect 9 has a hole here.

So that's a summary of my adventures. Now that I've been through it, if I had a choice, I would generally not recommend trying to create XML files outside of GP and send them to eConnect. It's tedious, time consuming, and error prone to reverse engineer the eConnect XML format. It is so much easier to use the .NET eConnect Serialization assemblies than to debug and validate XML files. But as this project demonstrated, sometimes the choices are limited, and you just have to go off-roading and figure out new solutions.