Inserting Data into XML Files

BIDS (aka Business Intelligence Studio, aka SQL Server Integration Services) generally does a pretty good job of inserting structured data into XML files. However, before we get into the nitty-gritty of creating a Data Flow task inside BIDS, you have to have a basic understanding of how T-SQL deals with XML formatting.

In order to display SQL data in XML format, you have to use the “FOR XML” command at the end of your T-SQL query.  SQL provides the following command options.


  • Begins each row of data with the table name or alias
  • If you have complex joins, it will try to figure out the main table names or alias and use that for the entire row (may not always work as expected)
    <table_name Element1="Value1" Element2="Value2"/>
    <table_name Element1="Value1" Element2="Value2"/>


  • Begins each row of data with a “row” designation
    <row Element1="Value1" Element2="Value2" />
    <row Element1="Value1" Element2="Value2" />
  • You can also add a string to replace “row” like this: FOR XML RAW(‘dirt’)
    <dirt Element1="Value1" Element2="Value2" />
    <dirt Element1="Value1" Element2="Value2" />


  • Wraps each row of data with the a “row” designation and wraps each data point with the column name
  • You can also add a string to replace “row” like this: FOR XML PATH(‘dirt’)


  • You can add a TYPE command to any of the above FOR XML commands to explicitly cast the results of the query into an XML data type like this: FOR XML RAW(‘dirt’), TYPE
  • The results of the query can be loaded into a variable and recalled with another select statement
Declare @xmlvariable xml

Set @xmlvariable = (select … from dbo.table_name for xml auto, type)

Select @xmlvariable


  • You can (or in many cases, have to) add a ROOT or a ROOT(‘mcstain’) command to any of the above commands like this: FOR XML RAW(‘dirt’), ROOT(‘mcstain’)
  • Wraps all of the data with a “root” designation or specified string
<dirt Element1="Value1" Element2="Value2" />
<dirt Element1="Value1" Element2="Value2" />


  • This essentially makes the results of AUTO and RAW look like PATH, and the command looks like this: FOR XML AUTO, ROOT(‘mcstain’), ELEMENTS


  • I’m not going to cover these here


  • This expects an specific column naming convention in order to create a universal table

Setting up the SSIS Package

  • First, you must create a blank XML file
    • This can be done in Explorer by right-clicking on the background of any folder and selecting New then Text Document
      • Simply rename the file to the desired name and change the extension from .txt to .xml
      • This file can now be viewed using Notepad, Excel, and Internet Explorer
    • This can also be done directly through Notepad
      • Save the file as type “All Files”, and Encoding “ANSI”
    • If you can’t do the above then you have failed as a human being
      • Feel free to hit your hand with a hammer
  • Create a Flat File Connection in the Connection Managers window
    • Provide a name for the Connection Manager
    • Click on the Browse button
      • Change the Files of type to All Files, select your XML file and click on the Open button
    • Click on Columns in the list on the left, which will just update your column information and click OK
  • Create an OLE DB connection in the Connection Managers window and connect it to your SQL Server data source
    • ADO.NET will also work, but it requires the addition of a Data Conversion between the Source and the Destination to convert data type DT_NTEXT to DT_TEXT
  • Create a Data Flow Task and double-click to open it
  • Create an OLE DB Data Flow Source
      • Double click on the Source to edit it
      • The data access mode must either be a SQL Command, or a SQL Command from variable (OLE DB only)
        • You can also build up a SQL Command using an expression (this is an ADO.NET workaround for creating a dynamic command similar in function to a “SQL Command from variable”
      • Create your SQL command in the SQL command text box
    SELECT TOP 100
    FROM dbo.table_name A
    for xml path('dirt'), ROOT('mcstain'), ELEMENTS
      • In order for this to be parsed correctly, the entire select statement must be cast as a varchar(max) and aliased to a column name
            SELECT TOP 100
            FROM dbo.table_name A
            FOR XML PATH('dirt'), ROOT('mcstain'), ELEMENTS
            ) as varchar(max)
         ) as Column0
    • Naming your column alias “Column0” is preferable because it will auto-map to the name BIDS gives the Output Column
      • Known fact: It also retroactively prevented Y2K (a la Terminator 2)
    • Go to the columns section of the Source Editor and make sure that External Column automatically maps to Output Column “Column0”
    • Exit the Source Editor
  • Create a Flat File Destination in your Data Flow and connect the Source to the Destination
      • Double click on the Destination to edit it
      • Select the appropriate Flat File connection manager in the drop-down menu (typically already selected for you)
      • Make sure that Overwrite data in the file is selected
      • For the Header, this is up to you, but here’s an example:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
  • Go to the Mappings section of the Destination editor and make sure that your Input Column is connected to your Destination Column
  • Close the Destination Editor, verify that there are no errors and run the package to test

Connecting to Excel .XLS* Files with Integration Services

Disclaimer: This post pertains to Business Intelligence Development Studio 2008 (aka BIDS, SSIS, Integration Services, as well as SQL Server Data Tools since then) . I have not tested this method with newer versions, so please share your experiences in the comments.

When Microsoft made the switch from Office/Excel 2003 to Office/Excel 2007, they made some drastic changes.  Most notably, the XLS file extension went away, replaced by the XLSX (Excel XML), XLMS ((Excel Macro-Enabled XML), and XLSB (Excel Binary) file format types.  XLSX became the default, while XLSB became the successor to XLS in a sense, as both use binary under the hood.  XLSM now because the only format that allows macros.

Under the hood, Microsoft switched from the Jet Database engine to ACE (Access Database Engine.  As a result, BIDS 2008 has two Excel versions options for Excel connections: Excel 97-2003 and Excel 2007 (which technically includes Excel 2010 as far as I know).

Apparently, BIDS didn’t get the memo (and 6 years later, still doesn’t have this), because selecting Microsoft Excel 2007 in the Excel version drop-down allows you to only pick the XLSX extension.  In the explorer browser, switching your Files of Type to All, and trying to open XLSM or XLSB file, you get this nasty message:

Fortunately, I was able to find a workaround.

1) First, create an XLSX copy of your workbook and point your Excel Connection Manager to it to gather the meta data
2) Go over to the Properties window
3) Find the ExcelFilePath property and edit the path to point to the desired XLSM or XLSB file.

That should be it!  One caveat is that if you want to delete the XLSX workbook, you will have to delay validation on any containers connecting to your workbook, as it will try to validate against the deleted XLSX file.  Also, the XLSX file doesn’t have to contain all of your data – just enough that SSIS can pick up the headers.