SQL XML Bulk Load (SQLXMLBULKLOADLib) is STA

This is a reminder to myself the next time I have problems getting the SQLXMLBULKLOAD library to work. According to several articles online, the bulk load capabilities Microsoft provides through this library only work in a single threaded environment. You can read a bit more about it over at sqlxml.org.

The basic gist is this. When you want to run the bulk load utility in another thread, specify to that thread that it should run as a single threaded apartment, like this:

System.Threading.Thread.CurrentThread.ApartmentState =
System.Threading.ApartmentState.STA;

You can also specify it when you create a thread by doing this:

System.Threading.Thread bulkLoaderThread
= new Thread( new ThreadStart( BulkLoadProc) );
bulkLoaderThread.ApartmentState = System.Threading.ApartmentState.STA;
bulkLoaderThread.Start();

For anyone who has never used this library before you may want to take a look at it. It provides a great way to take large amounts of XML and load them into a database. The usage of the library is as simple as instantiating the object, setting a few parameters, and then calling Execute() which takes the path to your schema file that provides mapping between XML fields and database fields, and a the path to the actual XML file you want to load.The library is limited in that it doesn’t provide any sort of record by record reporting or event hooking, however, it is a great tool for when you are sure you have valid XML going in (e.g. it expects you’ve already validated the XML and data integrity).

SQL XML 3.0 SP3 Download