This is the first thing I've ever had to do in SQL Server
Integration Services. I needed to create a package that output records
from a database to an xml file in the file system. The data was
retrieved using an OLE DB source and stored procedures (the easy part)
but because SSIS doesn't have an "XML Destination Component" creating
the xml file was not as straight forward as I had expected.
I found a great post from John Welch that explained how to do it using
a Script Component as the destination in your Data Flow. All the magic
happens in the script.
Below is my modified code for the Script Component - I use the
XMLTextWriter instead of a StreamWriter for ease and I also added a
'Nothing' check during row processing to avoid errors with null data.
The output file name is retrieved from a package variable.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Xml
Imports System.IO
Imports System.Reflection
Public Class ScriptMain
Inherits UserComponent
Dim sw As StreamWriter
Dim xWriter As XmlTextWriter
Dim rowName As String
Dim collectionName As String
Private columnNames As Integer()
Public Overrides Sub PreExecute()
rowName = "Company"
collectionName = "Companies"
Dim fileName As String = CType(Me.Variables.outputFileName, String )
fileName = fileName & (Format(Now(), "yyMMdd").ToString) & ".xml"
xWriter = New XmlTextWriter(fileName, Nothing)
xWriter.WriteStartDocument()
xWriter.WriteStartElement(collectionName)
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
Dim rowType As Type = Row.GetType()
Dim columnValue As PropertyInfo
With xWriter
.WriteStartElement(rowName)
For
Each column In
Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnValue = rowType.GetProperty(column.Name)
.WriteStartElement(column.Name)
Dim value As Object
value = columnValue.GetValue(Row, Nothing)
If Not value Is Nothing Then
.WriteValue(value.ToString())
End If
.WriteEndElement()
Next
.WriteEndElement()
End With
End Sub
Public Overrides Sub PostExecute()
xWriter.WriteEndElement()
xWriter.WriteEndDocument()
xWriter.Close()
End Sub
End Class
Simple huh! For Johns original post go here
Jimmy