Export to XML Using SSIS

Recently I was given a task to export data in XML format using SSIS.
I initially thought it should be straight forward – just dump it in a flat file and
give it a name xml type, however SSIS has no XML destination just an XML source. I had no luck dumping the XML in
flat file. My solution was to use a script task which worked well.

My requirement is that the stored
procedure would return the XML as a single row and I need to export that in a
file.

I will run through this solution by example. Below is my stored procedure, which will return an XML row

        ALTER PROCEDURE ExporttoExcel
AS
BEGIN
DECLARE @XML XML
SELECT @XML = (SELECT DataXML FROM
                        (SELECT name as "name" ,
                        object_id as "objectid"
                        FROM sys.objects
                        FOR XML PATH ('Objects'), ROOT('AllObjects')
                        )D(DataXML))                       

SELECT  DataXML =@XML
END
    

The output of the above stored procedure will
be as below :


<AllObjects>
<Objects>
<name>sysrscols</name>
<objectid>3</objectid>
</Objects>
<Objects>
<name>sysrowsets</name>
<objectid>5</objectid>
</Objects>
… 

</AllObjects> 

Now the stored procedure is created, let
us start with the SSIS package. The first task is to drag and drop the Execute SQL
Task. This Task will execute the stored procedure and return  the result set of
XML in a string variable.

Next configure the properties inside Execute SQL task

Note that, the Result Set will be XML,
and ConnectionType will be ADO.NET. In order to return an XML result only the ADO.NET
Connection Manager has to be used. Be careful while setting the parameters to
your stored procedure in ADO.NET connection, as the parameter passing method is
different in ADO.NET and OLEDB. In ADO.NET you need to pass the parameter as
@para instead of ?

Here, we dont have any parameters, so we
will ignore this. We declare three package level variables

DataXML – Will contain the XML returned by
the stored procedure

FileName – If there is specific file name
to be given, set it as a variable.

FilePath – If the file location is also
variable, this variable will be set.

On the Result Set select the variable
defined.

Here, when we store the XML value in a string
variable the XML display format changes to one single line and it no longer
displays in a proper XML format.

So, the next step is to add a Script task

Set the three variable defined earlier as
Read only variables inside the script task :

Edit the Script and go to the void main
function. The first step in the script task, is to fetch the data from the DataXML
string variable into a new variable inside the task. Since the variable is a
string and we want to export to a XML file, we need to convert the string
variable to an XMLDocument. Declare an xdoc XMLDocument variable, and using the
LoadXML function, convert the string to XML. Put the outputfile path into another
variable. Lastly, write the xdoc variable to the
outputfile path. Save the script and exit.

        public void Main()

        {
            // TODO: Add your code here

            string Data = Dts.Variables["DataXML"].Value.ToString();
                         
            System.Xml.XmlDocument xdoc =  new XmlDocument();
            xdoc.LoadXml(Data.ToString());
            string outputFile = Dts.Variables["FilePath"].Value.ToString() + "\\" + Dts.Variables["FileName"].Value.ToString();
            xdoc.Save(outputFile);                  

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    

Now, run the package :

The file is now exported to the expected
path.

Next, open the file :

The file has a new Root tag which means
when the Execute SQL tasks writes the DataXML variable with XML result set, it
always adds a Root Tag. Since we don’t need the extra Root tag, we
can remove it in the script task. Replace the earlier code to get xml in Data
variable with the new code as below.

        public void Main()

        { 
            // TODO: Add your code here 
           // string Data = Dts.Variables["DataXML"].Value.ToString(); 

Dts.Variables["DataXML"].Value.ToString().Replace("<ROOT>", "").Replace("</ROOT>", "");
 
            System.Xml.XmlDocument xdoc =  new XmlDocument();

            xdoc.LoadXml(Data.ToString());
            string outputFile = Dts.Variables["FilePath"].Value.ToString() + "\\" + Dts.Variables["FileName"].Value.ToString();
            xdoc.Save(outputFile);                 
            Dts.TaskResult = (int)ScriptResults.Success;       
        }
         
    

Now after running the package, the file is
as expected




Array

No comments yet... Be the first to leave a reply!