Thursday, August 6, 2009

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.

5 comments:

Janakiram M.P. said...

Steve,

Great Post detailing your experiences on econnect. I think those who are new to econnect must read this post to understand the good and bad part of it. Reading your experiences, I thought you might want to ask your client for a quick upgrade to V10 that makes your life and their life easier, i guess in terms of econnect!!

Mariano Gomez said...

When dealing with this many transactions, it's best to have a message broker. This is where BizTalk Server comes into play, namely working with envelopes and Orchestrations. In the case of 47,000 invoices in an XML file, you can use an envelope to separate these into 47,000 different messages that can be submitted to an Orchestration on a separate host. Orchestrations can also be configured to control the flow of data going to the eConnect adapter.

Best regards,

MG.-
Mariano Gomez, MVP

Steve Endow said...

Thanks Mariano. Good point, and it's definitely a suggestion I've heard before.

BizTalk and other queue based solutions are options, and I've even had discussions with a developer who insisted that SQL Message Broker is the obvious solution, but I tend to try and avoid them for most of my clients because of the additional cost, infrastructure, and/or complexity such solutions can introduce.

For example, I just checked BizTalk 2009 Standard licensing, and it looks like the single processor license is anywhere from $8,500 to $12,000, which is pretty pricey if it's only being used for a GP integration.

For single source integrations to GP, a simple .NET app either talking to SQL Server or using basic file management has been very simple and low cost, and the client doesn't need any extra infrastructure or licensing. And best of all, they are comfortable clicking an icon on the desktop or using Windows Scheduler to manage an automated integration.

I do have one client that uses BizTalk to manage integrations with numerous external sites and trading partners, and we have discussed rolling a few of their GP integrations over to BizTalk, which makes sense since they have the knowledge and infrastructure in place, and would theoretically allow them to centralize their integrations. But so far they haven't been compelled to convert them as they always seem to have more pressing issues.

If there's more to such solutions that I'm missing, I'd be interested in hearing the other benefits that might be relevant for a GP integration.

Thanks,

Steve

Lou Spevack said...

Be thankful that you did not have long SOP document numbers. If you would assume that just because GP will accept a 21 character SOP document number then eConnect will, you would be wrong. eConnect will only accept a 20 digit SOP document number. Microsoft will fix this in a future service pack.

Steve Endow said...

Thanks Lou. Yes, I've researched the field length issue. Based on my tests with GP 9, in most cases, eConnect does not complain at all about a value that exceeds the field length (even 50 chars for a 20 char field). It simply truncates the value.

I haven't tested it in GP 10, but I now add field length testing as part of my validation routine that is performed on all data prior to sending it to eConnect.