I recently needed to load several thousand XML files into a SQL database, I created an SSIS package which was created as followed:

Using a foreach container to loop through a directory and load each file path into a variable, the “Import XML” dataflow would then load each XML file into a SQL table.

Running this, it took approximately 1 second to load each file which seemed a massive amount of time to parse the XML and load the data, speaking to my colleague Martin Croft, he suggested the use of T-SQL Bulk Insert and OpenRowset, so we adjusted the package as followed:

The same foreach container was used but instead the following SQL command was executed (this is an expression):

"INSERT INTO MyTable(FileDate)
SELECT   CAST(bulkcolumn AS XML)
    FROM OPENROWSET(
        BULK
        '" + @[User::CurrentFile]  + "',
        SINGLE_BLOB ) AS x"

Using this method we managed to load approximately 20 records per second, much faster…for data loading!

For what we wanted to achieve this was perfect but I’ll leave you with the following points when making your own decision on which solution you decide to choose! 

Openrowset Method

• Much faster to get the data into SQL
• You’ll need to parse or create a view over the XML data to allow the data to be more usable(another post on this!)
• Not able to apply validation/transformation against the data when loading it
• The SQL Server service account will need permission to the file
• No schema validation when loading files

SSIS

• Slower (in our case)
• Schema validation
• Allows you to apply transformations/joins to the data
• Permissions should be less of a problem
• Data can be loaded into the final form through the package
• When using a schema validation errors can fail the package (I’ll do another post on this)