XML Transformations Part 2

Previously, I posted about using a script destination component to output an XML file. A post on the MSDN forums got me thinking about a slight tweak to the previous post that would result in a little more flexibility. The poster wanted to create an XML string containing his row values to save into a column of a database table. So instead of script destination, what was needed here was a script transform to create a new column.

The data flow is straightforward – a source, script transform, and a OLEDB Destination.

In the script component, each column from the input file is selected. I added a single output column (XML) to hold the results of the script task.

The script used is below:

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

Public Class ScriptMain
    Inherits UserComponent

    Private Const rowElement As String = "Row"

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Dim column As IDTSInputColumn90
        Dim rowType As Type = Row.GetType()
        Dim columnValue As PropertyInfo

        Dim newXML As String

        newXML = (FormatElement(rowElement))
        For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
            columnValue = rowType.GetProperty(column.Name)
            newXML += ((FormatElement(column.Name) + columnValue.GetValue(Row, Nothing).ToString() + FormatElement(column.Name, True)))
        Next
        newXML += (FormatElement(rowElement, True))
        
        Row.XML = newXML
    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

The script is very similar to the one from the previous article, though it is a bit simpler. The XML values are prepared by iterating through the Input Collection metadata, and using the Reflection classes to read the Row object dynamically. The resulting value is put into the XML column on the output.

The OLEDB Destination simply maps the XML column to an XML column on a database table.

This is a good alternate approach to the previous one, particularly if you need to send the XML to a database instead of a flat file.