While creating the EDI Warehouse, for EDI files that were large (10mb transactions), I was seeing 1 of two things with the SQL Adapter:
- Out of memory exceptions
- 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)
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.