XML Data Destination

I had the pleasure of coding a Data Source that would output data in an XML format, thought I would share the code. I cannot take full credit for the code as it originated from Jamie Thomson’s blog and I’ve just adapted it so it’s a little more dynamic and capable of multi-levels within a row. There is potential to improve the code further or to customise it to your needs.

Why bother creating a Destination?

Seems a sensible question to ask when you could quite easily use an Execute SQL Task and have a ResultSet of XML. I found this worked well on small sets of data, but as the volumes of data grew, the time it took to execute the task grew exponentially. The package also seems to be incredible resource intensive on the server. Creating the script reduced loads from hours (some loads never finished as the package threw an error after 10 hours processing) to a few minutes.

The Code:

For the code to work the naming of the columns into the destination task need to be in a specific format, for those of you who may have used SQL with a FOR XML EXPLICIT clause, it’s the same structure.

Pass in all the columns that you want to output in XML Format, as shown below:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components


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

Public Class ScriptMain
Inherits UserComponent
Dim sw As StreamWriter
Dim xWriter As XmlTextWriter
Dim NewMainTag As Integer
Dim strAttributeName As String
Dim columnValue As PropertyInfo
Dim strValue As String


Public Overrides Sub PreExecute()
'Read Only variables (strDestinationFolder and strXMLFileName)
Dim gsPickUp As String = Me.Variables.strDestinationFolder.ToString
Dim gsPickUpFilename As String = Me.Variables.strXMLFileName
Dim fileName As String = gsPickUp & "\" & gsPickUpFilename
fileName = fileName & (Format(Now(), "yyyyMMddhhmmss").ToString) & ".xml"
xWriter = New XmlTextWriter(fileName, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteStartElement("document")
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'declaration of varaibles
Dim column As IDTSInputColumn90
Dim rownumber As String
Dim rowType As Type = Row.GetType()
Dim StringIndex As String
Dim TagName As String
Dim objColumn As Object
Dim FirstTimeForTag As Integer = 0
If Row.Tag = 2 Then
'if start of document
If NewMainTag = 0 Then
NewMainTag = 1
xWriter.WriteStartElement("Row")
Else
'create new row and end pervious
xWriter.WriteEndElement()
xWriter.WriteStartElement("Row")
End If
End If

'for each column in the row
For Each column In ComponentMetaData.InputCollection(0).InputColumnCollection
'convert tag number to string
rownumber = CType(Row.Tag, String)
'create tag lookup in column (e.g. !2! or !3!)
StringIndex = "!" + rownumber + "!"

'if tag number matches column number
If column.Name.IndexOf(StringIndex) > -1 Then
'extract name of tag from column before tag number(e.g. row from row!2!)
TagName = column.Name.Substring(0, column.Name.IndexOf(StringIndex))

'if not the main tag and first entering the loop out put a tag header
If Not (Row.Tag = 2) And FirstTimeForTag = 0 Then
xWriter.WriteStartElement(TagName)
FirstTimeForTag = 1
End If

'attribute names do not contain exclamation marks, remove !
strAttributeName = column.Name.Replace("!", "")
'get column values
columnValue = rowType.GetProperty(strAttributeName)
objColumn = columnValue.GetValue(Row, Nothing)

'if column is not null output it to xml file (does not include blanks)
If IsNothing(objColumn) Then
Else
strValue = objColumn.ToString()
xWriter.WriteStartElement(column.Name.Replace(TagName + StringIndex, "").Replace("!element", ""))
xWriter.WriteString(strValue)
xWriter.WriteEndElement()
End If
End If
Next column

'end of element part
If Not (Row.Tag = 2) Then
xWriter.WriteEndElement()
End If
End Sub

Public Overrides Sub PostExecute()
'EOF
xWriter.WriteEndElement()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End Sub

End Class


And that should be about all you have to do.

Happy Exporting!