Feb 182009

While creating the EDI Warehouse, symptoms for EDI files that were large (10mb transactions), ampoule I was seeing 1 of two things with the SQL Adapter:

  1. Out of memory exceptions
  2. Taking hours to load data with high sql server usage

In opening a ticket with Microsoft to discover that the SQL adapter casts the message to a string in a certain part of the code, I knew that I could not use it for consistent high volume usage.

I created a Bulk XML Load adapter that accomplishes in 1 minute that previously took 3 hours to load.

This is the interface:


Some of the features are:

If the size of the message is small enough (as defined in the send handler properties) it takes the message and directly loads the data into the database. You can override the direct Stream to Database setting by changing the File Load boolean to True or if the message size is greater than the send handler property (by default set at 10mb)

Here is a screen shot of the send adapter handler, where you define where the schema needs to reside:


A sample schema’s http://targetnamespace/folder#RootNode turns into (starting in targetnamespace position 7) targetnamespace_folder_rootnode.xsd

Sample Schema Example

Enables all of the features of Bulk XML load 4.0

If an error occurs during the load, will create the xml data it is trying to load, the error.xml and a Visual Basis Script to re run the process for further testing (as long as the Debug Flag is set to True).

In the Event Log you see this entry:


This allow to manually run the bulk load to determine the error.

  • Mike Lile

    I’ve run in to a similar problem and had considered developing a similar adapter; do you have plans to release what you’ve done to the general public?

  • I am right now determining a price for this, as I am finding more and more uses for this as a standalone adapter.

  • Vlad

    Microsoft BizTalk Adapter Pack 2 includes the WCF SQL Adapter that can be used for consistent high volume usage. It has been tested to load a huge message without the Out of memory exception.

  • Here is the price for the adapter: http://stottis.com/services.html

  • Mike

    We are interested in purchasing your product.
    Does the price give us the source code?
    Is it possible to evaluate this product or see a demo?

  • This is a great project and I hope you get a lot of support!

  • Thanks for this stand alone adapter. What is the price? Let me know!

  • This concept is fine and interesting!

    Thank you.

  • Hello Kitty !

  • r the adapter: http: