Tuesday, November 11, 2008

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.

3 comments:

  1. An easier solution may be to use a third party XML Destination Adapter such as the one from Keelio Software (http://www.keelio.com). You'll get much more flexibility then what you are doing with the code sample you provided.

    ReplyDelete
  2. Yes, this is true and I know the destination from that company.

    However, most XML destinations I use an XSLT later on anyway, so this is method is perfectly okay for me.

    ReplyDelete
  3. You could also use System.Xml directly (adding reference to System.Xml on script task):

    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.Xml
    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 = "c:\\test3.xml" ' CType(Me.Connections.Destination.AcquireConnection(Nothing), String)
    End Sub

    Private xmldoc As XmlDocument
    Private xmlnode As XmlNode
    Private xmlroot As XmlElement
    Private xmlelem As XmlElement
    Private xmltext As XmlText
    Public Overrides Sub PreExecute()
    xmldoc = New XmlDocument()
    xmlnode = xmldoc.CreateNode(XmlNodeType.XmlDeclaration, "", "")
    xmldoc.AppendChild(xmlnode)
    xmlroot = xmldoc.CreateElement(rootElement)
    xmldoc.AppendChild(xmlroot)
    ' set date & time
    xmlelem = xmldoc.CreateElement("info")
    xmlelem.InnerText = "DataFlow processing - " & DateTime.Now.ToString()
    xmlroot.AppendChild(xmlelem)

    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()
    xmldoc.Save(targetFile)
    End Sub

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

    While Buffer.NextRow()
    xmlelem = xmldoc.CreateElement(rowElement)
    Dim counter As Integer
    counter = 0

    For Each index As Integer In columns
    Dim value As Object = Buffer(index)
    xmlnode = xmldoc.CreateNode(XmlNodeType.Element, columnames(counter).ToString(), Nothing)
    xmlnode.InnerText = value.ToString()
    xmlelem.AppendChild(xmlnode)
    counter = counter + 1
    Next
    xmlroot.AppendChild(xmlelem)
    End While
    End Sub
    End Class

    ReplyDelete