Import XML into SQL Server using SSIS

Filed under: ssis sqlserver xml

This example was written using SQL Server 2008 R2.  In SQL Server 2005, Microsoft introduced the XML column data type.  I find it much easier working with XML directly in SQL Server over using the built-in XML Source component in SSIS.

This example shows loading two XML from the file system into a SQL Server table using SSIS. 

Package Setup

First, setup a Foreach Loop Container (FELC) to loop over the directly in question and assign the file name to a variable.  There are many articles about using a FELC to read a list of files in a directory.

Within the FELC, add a Data Flow Task (DFT).  The steps in the DFT will be responsible for:

  1. Extracting the XML file contents from the file system (Script Component).
  2. Converting those contents to Unicode (Data Conversion).
  3. Storing those contents into a SQL server table (OLE DB Destination).

Script Component

The script component is setup to take two input Read Only Variables: the directory and the current file name being processed as passed from the FELC.  Here’s a screenshot of that input:

Two output columns are then configured: the first captures the XML’s file name in a string and the second captures the XML file contents in a text_stream column.  Shown below is the configuration for that column:

The C# script itself is pretty straightforward with reading the file contents into a byte array then adding that to the output buffer:

    public override void CreateNewOutputRows()
    {

        // assemble the path location of the XML file
        string xml_filepath = Variables.sourcedir + Variables.xmlfilename;


        // create a byte array to store the XML document
        byte[] xml_file_contents = System.IO.File.ReadAllBytes(xml_filepath);

        // add a row to our output populating our custom columns with the XML file name and the actual contents.
        Output0Buffer.AddRow();
        Output0Buffer.xmlfilename = Variables.xmlfilename.ToString();
        Output0Buffer.xmlfilecontentstextstream.AddBlobData(xml_file_contents);
 
    }

Data Conversion

Now that the XML file contents are flowing down the DFT, the next step is to convert those contents to a Unicode text stream using a Data Conversion transformation.

Storing in SQL Server

The final step is mapping the available columns to our SQL Server table using an OLE DB destination.  Here is the entire DFT that’s contained within the FELC:

The two XML files loaded were rather small:

Here they are loaded in SQL Server:

I used this approach to load XML files in > 1 GB.  The XML content is now stored in SQL Server making it relatively easy to pull data out using SQL Server’s built-in XML methods.

Questions?

Send me an email: jontav@yahoo.com

Share