Code für die XML Destination Script Komponente

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.Globalization

Public Class ScriptMain
    Inherits UserComponent

    Dim xWriter As XmlTextWriter
    Dim First As Boolean
    Dim SalesOrderID As String
    Dim InvC As CultureInfo

    Public Overrides Sub PreExecute()
        Dim ConnectionString As String
        InvC = New CultureInfo("en-US", False)

        'Me.ComponentMetaData.FireInformation(0, "PreExecute", "PreExecute", "", 0, True)
        ConnectionString = Me.Connections.XMLOut.ConnectionString
        xWriter = New XmlTextWriter(ConnectionString, System.Text.Encoding.UTF8)

        With xWriter
            .WriteStartDocument()
            .WriteComment("Beispiel für XML Destination in SSIS - Willfried Faerber")
            .WriteStartElement("x", "root", "http://www.sqlis.de")
            '---------------- mit der Formatierung wird es sehr langsam und die Datei wird sehr groß
            '.Formatting = Formatting.Indented
            '.Indentation = 3
        End With
        First = True
        SalesOrderID = ""
        'Me.ComponentMetaData.FireInformation(1, "PreExecute", "PreExecute", "", 0, True)
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
        Try
            If SalesOrderID <> Row.SalesOrderID.ToString Then
                'Me.ComponentMetaData.FireInformation(0, "Input0_ProcessInputRow", "Input0_ProcessInputRow", "", 0, True)
                If Not First Then
                    xWriter.WriteEndElement() ' "Sales header" vorheriger Bereich abschließen
                    First = False
                    'Me.ComponentMetaData.FireInformation(1, "Input0_ProcessInputRow", "Input0_ProcessInputRow", "", 0, True)
                End If

                SalesOrderID = Row.SalesOrderID.ToString
                With xWriter
                    .WriteStartElement("SalesHeader")
                    .WriteElementString("SalesOrderId", SalesOrderID)
                    .WriteElementString("OrderDate", Row.OrderDate.ToString("s"))
                    If Not Row.PurchaseOrderNumber_IsNull Then
                        .WriteElementString("PurchaseOrderNumber", Row.PurchaseOrderNumber.ToString())
                    Else
                        .WriteElementString("PurchaseOrderNumber", "")
                    End If
                    '------------------------------------------
                    .WriteStartElement("SalesDetail")
                    .WriteElementString("UnitPrice", Row.UnitPrice.ToString("#.##", InvC))
                    .WriteElementString("OrderQty", Row.OrderQty.ToString())
                    .WriteElementString("ProductID", Row.ProductID.ToString())
                    .WriteEndElement()  'Sales Detail
                    '-------------------------------------------
                    'Me.ComponentMetaData.FireInformation(2, "Input0_ProcessInputRow", "Input0_ProcessInputRow", "", 0, True)
                End With
            Else
                With xWriter
                    '------------------------------------------
                    .WriteStartElement("SalesDetail")
                    .WriteElementString("UnitPrice", Row.UnitPrice.ToString("#.##", InvC))
                    .WriteElementString("OrderQty", Row.OrderQty.ToString())
                    .WriteElementString("ProductID", Row.ProductID.ToString())
                    .WriteEndElement()  'Sales Detail
                    '------------------------------------------
                    'Me.ComponentMetaData.FireInformation(3, "Input0_ProcessInputRow", "Input0_ProcessInputRow", "", 0, True)
                End With
            End If
        Catch ex As Exception
            MsgBox("Input0_ProcessInputRow" & vbNewLine & ex.ToString)
        End Try
    End Sub

    Public Overrides Sub PostExecute()
        Try
            Me.ComponentMetaData.FireInformation(0, "PostExecute", "PostExecute", "", 0, True)
            With xWriter
                .WriteEndElement() ' "Sales header" vorheriger Bereich abschließen
                .WriteEndElement()  ' "Root" abschließen
                .WriteEndDocument()
                .Close()
            End With
            Me.ComponentMetaData.FireInformation(1, "PostExecute", "PostExecute", "", 0, True)
        Catch ex As Exception
            MsgBox("PostExecute" & vbNewLine & ex.ToString)
        End Try
    End Sub

End Class

Das Beispielpaket finden Sie hier