Script Destination Component Code Sample Here That Creates A Csv Or Xml File

After much work and thanks to all of you who helped on this here is a code sample that can be adapted. From the dataflow task add an OLEDB source component, a row count component and finally a Script Destination Component.

On the Script Destination Component rename the Input node of the imports and outputs tree view to "ParsedInput"

The readonly User: variables that start with gs can be read in the PreExecute method

The readwrite User: variable giSuccessCount can only be used in the post execute task because it is populated by the Row Count Component which is the previous object in the Dataflow

The xml code is adapted from an idea in Donald Farmers book

enjoy

Dave

Now if someone can make a Script Source Component that can read a file with a header , data body and trailer that would b egreat!
' 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
 
Public Class ScriptMain
    Inherits UserComponent
    Dim sw As StreamWriter
    'In addition to using the Imports System.Xml statement a reference must be added to the
    'System.Xml assembly  (Select Project-Add Reference from IDE)
    Dim xWriter As XmlTextWriter
    Dim OutputFileType As String '.csv or .xml
 
 
 
    Public Overrides Sub PreExecute()
       
        'Read Only variables
        Dim gsPickUp As String = Me.Variables.gsPickUp 'D:ftprootOutAvid'
        Dim gsPickUpFilename As String = Me.Variables.gsPickUpFilename '1_AVID_'
        Dim gsPickUpFileExtn As String = Me.Variables.gsPickUpFileExtn '.csv'
        Dim gsMemoText As String = Me.Variables.gsMemoText 'Memo Text : credit adjustment'
        Dim gsStatementText As String = Me.Variables.gsStatementText 'Statment Text : credit adjustment'
        Dim gsRunMode As String = Me.Variables.gsRunMode 'UPDATE'
        Dim fileName As String = gsPickUp & "" & gsPickUpFilename
        fileName = fileName & (Format(Now(), "yyMMdd").ToString)
        'MsgBox(fileName)
 
        OutputFileType = gsPickUpFileExtn
        If OutputFileType = ".csv" Then
            fileName = fileName & gsPickUpFileExtn
            sw = New StreamWriter(fileName) 'connection to dest file
 
            'Header records
            sw.Write(gsRunMode)
            sw.Write(Environment.NewLine) ' end of line
            sw.Write(gsMemoText)
            sw.Write(Environment.NewLine)
            sw.Write(gsStatementText)
            sw.Write(Environment.NewLine)
            sw.Write(Environment.NewLine) 'Spacer
        End If
 
        If OutputFileType = ".xml" Then
            fileName = fileName & gsPickUpFileExtn
            'xWriter = New XmlTextWriter(Me.Connections.XMLConnection.ConnectionString, Nothing)
            'xWriter.WriteStartDocument()
            'xWriter.WriteComment("Customer file parsed using script")
            'xWriter.WriteStartElement("x", "customer", "http://some.org/name")
            'xWriter.WriteAttributeString("FileName", Me.Connections.XMLConnection.ConnectionString)
            xWriter = New XmlTextWriter(fileName, Nothing)
            xWriter.WriteStartDocument()
            xWriter.WriteComment("Customer file parsed using script")
            xWriter.WriteStartElement("x", "customer", "http://some.org/name")
            xWriter.WriteAttributeString("FileName", fileName)
        End If
 
 
    End Sub
 
 
 
    Public Overrides Sub ParsedInput_ProcessInputRow(ByVal Row As ParsedInputBuffer)
 
 
        If OutputFileType = ".csv" Then
            Dim delim As String = ","
 
            sw.Write(Row.ProjectID.ToString + delim)
            sw.Write(Row.TransactionRefNum.ToString + delim)
            sw.Write(Row.CustomerNum.ToString + delim)
            sw.Write(Row.AccountNum.ToString + delim)
                         
            sw.Write(Environment.NewLine) ' end of line
            sw.Flush() 'send the stream to file
        End If
 
 
 
        If OutputFileType = ".xml" Then
            xWriter.WriteStartElement("CUSTOMER")
 
            xWriter.WriteStartElement("ProjectID")
            xWriter.WriteString(Row.ProjectID.ToString)
            xWriter.WriteEndElement()
 
            xWriter.WriteStartElement("TransactionRefNum")
            xWriter.WriteString(Row.TransactionRefNum.ToString)
            xWriter.WriteEndElement()
 
            xWriter.WriteStartElement("CustomerNum")
            xWriter.WriteString(Row.CustomerNum.ToString)
            xWriter.WriteEndElement()
 
            xWriter.WriteStartElement("AccountNum")
            xWriter.WriteString(Row.AccountNum.ToString)
            xWriter.WriteEndElement()
 
             
 
        End If
 
 
 
       
       
    End Sub
 
    Public Overrides Sub PostExecute()
 
       
 
        If OutputFileType = ".csv" Then
            'Create the trailer
            sw.Write(Environment.NewLine) ' blank line
            sw.Write("RECORD_COUNT: " & Me.Variables.giSuccessCount.ToString) 'ReadWrite Varible
            sw.Write(Environment.NewLine)
            sw.Flush() 'send the stream to file
            'Close file
            sw.Close()
        End If
 
        If OutputFileType = ".xml" Then
            xWriter.WriteStartElement("RecordCount")
            xWriter.WriteString(Me.Variables.giSuccessCount.ToString)
            xWriter.WriteEndElement()
            xWriter.WriteEndElement()
 
            xWriter.WriteEndDocument()
            xWriter.Close()
        End If
 
    End Sub
 
 
End Class