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.