In this blog we will see how to export XML file using SSIS. I chose to demonstrate XML but it works the same for all BLOB strings (text, ntext).
Let us export this simple xml query to XML file
select cast('<!--?xml version="1.0" encoding="UTF-16" ?-->'+ ( SELECT TOP(20) CAST( '<' + FirstName +'_'+ LastName + '>'+ (SELECT child.FirstName, child.LastName FROM person.person child FOR XML PATH('')) + '' AS xml) as SSAXML FROM person.person parent for xml PATH(''), ROOT('person') ) as XML) as xmloutput ; |
if we execute the query on SSMS for AdventureWorks database we will get this an XML that looks like this one.
<person> <Syed_Abbas> <FirstName>Syed</FirstName> <LastName>Abbas</LastName> <FirstName>Catherine</FirstName> <LastName>Abel</LastName> <FirstName>Kim</FirstName> <LastName>Abercrombie</LastName> </Syed_Abbas> </person> |
Let us export this xml query to person.xml file. Let us begin by adding a data flow task
Now add an OLEDb source and add the XML Script to it
Now drop a Script component and select Destination type
Now open the Script Component and check the XML input column
Then select the script tab and click on Edit Script…
Include System.IO library to your script.
Now update your Input0_ProcessInputRow function as
public override void Input0_ProcessInputRow(Input0Buffer Row) { byte[] blob; string fileContent; blob = Row.xmloutput.GetBlobData(0, int.Parse(Row.xmloutput.Length.ToString())); fileContent = System.Text.Encoding.ASCII.GetString(blob); TextWriter tw = new StreamWriter(@"D:\person.xml"); tw.WriteLine(fileContent); tw.Close(); } |
Where xmloutput is going to be replaced by your XML column name. Finally execute your package
Now let us check if our XML file is created properly
Yap it is created and filled with our data properly.
Update 3/30/2014:
Kevin asked me he found additional character at the end of the XML file he generated and a way to remove that character.
There is a carriage return at the end of the XML file. To remove that we could add another script task after the data flow task that generates the XML.
The open of the Script task and add the following C# script.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; using System.Text; using System.Text.RegularExpressions; public void Main() { string strPath = @"D:\person.xml"; string fileContent = System.IO.File.ReadAllText(strPath); try { System.IO.File.WriteAllText(strPath, Regex.Replace(fileContent, "\r\n", "") .Replace("\'", """) .Trim()); Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception ex) { throw ex; } Dts.TaskResult = (int)ScriptResults.Success; } /*Replace the D:\person.xml with your actual path and file name of your xml*/ |
It should look like this on your SSDT or BIDS
This should fix the problem with the carriage return and check your XML file and to make sure.