SSIS XML Destination

Today, I needed to create a transmogrification that reads from a SQL Server table and creates a XML file.

The destination format included 98% mandatory fields and only two percent of data from the table. Easy: Read the data from SQL Server into a very simple XML file and then use a XSLT style sheet to transform it into the destination XML format.

First problem: SSIS does not have a XML destination. Fortunately, I found the following code that you can use inside a destination script component (Create a new data flow, drop a script component on it, select “Destination” as type and then copy and paste this code into the component, overwriting any existing code).


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO
Imports System.Reflection

Public Class ScriptMain
Inherits UserComponent

Private rootElement As String = "Root"
Private rowElement As String = "Row"

Private targetFile As String
Private xmlWriter As StreamWriter
Private columns As Integer()
Private columnames As String()

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
targetFile = CType(Me.Connections.Destination.AcquireConnection(Nothing), String)
End Sub

Public Overrides Sub PreExecute()
xmlWriter = New StreamWriter(targetFile, False)
xmlWriter.WriteLine(FormatElement(rootElement))

Dim input As IDTSInput90 = ComponentMetaData.InputCollection(0)

ReDim columns(input.InputColumnCollection.Count)
columns = Me.GetColumnIndexes(input.ID)

Dim column As IDTSInputColumn90
ReDim columnames(input.InputColumnCollection.Count)

Dim counter As Integer
counter = 0

For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnames(counter) = column.Name
counter = counter 1
Next

End Sub

Public Overrides Sub PostExecute()
xmlWriter.WriteLine(FormatElement(rootElement, True))
xmlWriter.Close()
End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

While Buffer.NextRow()
xmlWriter.Write(FormatElement(rowElement))

Dim counter As Integer
counter = 0

For Each index As Integer In columns
Dim value As Object = Buffer(index)
xmlWriter.Write(FormatElement(columnames(counter).ToString()) value.ToString() FormatElement(columnames(counter).ToString(), True))
counter = counter 1
Next

xmlWriter.WriteLine(FormatElement(rowElement, True))
End While
End Sub

Private Function FormatElement(ByVal elementName As String) As String
Return FormatElement(elementName, False)
End Function

Private Function FormatElement(ByVal elementName As String, ByVal closingTag As Boolean) As String
Dim returnValue As String

If closingTag Then
returnValue = "</"
Else
returnValue = "<"
End If

returnValue = elementName ">"
Return returnValue
End Function

End Class


Please note: I ripped this code from somewhere on the internet but I was not able to find out the original author.


Using this code, you will get a very simple XML file that you can transmogrify using XSLT. Because this should happen within SSIS, the best way to use a command-line XSLT processor and the best one I was able to find was nsxlt2.

Just drop an “Execute process” component to launch nsxlt2 and configure the arguments like this:

\\srv\test\Source.xml \\srv\test\SimpleXML2Destination.xslt -o \\srv\\test\DestFormat.xml

And you are done.