CODEWITHSUNDEEP
vb.netfile-iossisout-of-memory
lch
lch

Reputation: 193

System.out of Memory Exception for String Builder in SSIS Script Task

I am using a VB script in SSIS Script Task to add header and Trailer to a flat file. The code was working fine until recently i came across a problem where the rows in the file are more than usual and resulting in a failure on script task with error`Error:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
   at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
   at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
   at System.Text.StringBuilder.Append(Char[] value, Int32 startIndex, Int32 charCount)
   at System.IO.StreamReader.ReadToEnd()
   at System.IO.File.ReadAllText(String path, Encoding encoding)
   at System.IO.File.ReadAllText(String path)`

Can any one help me in fixing the problem please.I think instead of "String Builder" i need to use other string related method. I am getting error at fileContents.Append(File.ReadAllText(Dts.Connections("DestinationConnection").ConnectionString))

Here is my code:

Public Sub Main()

    Dim fileContents As New StringBuilder()
    Dim finalfile As String
    Dim firstline As String
    Dim lastline As String


    Dts.VariableDispenser.LockForRead("FirstLine")
    Dts.VariableDispenser.LockForRead("LastLine")

    Dts.VariableDispenser.LockForRead("FileName")

    firstline = CType(Dts.Variables("FirstLine").Value, String)
    finalfile = CType(Dts.Variables("FileName").Value, String)
    lastline= CType(Dts.Variables("LastLine").Value, String)


    'Write header, then append file contents and write back out.
    fileContents.AppendLine(String.Format("{0}", firstline))
      fileContents.Append(File.ReadAllText(Dts.Connections("DestinationConnection").ConnectionString))
    fileContents.AppendLine(String.Format("{0}", lastline))

    File.WriteAllText(finalfile, fileContents.ToString())

    Dts.TaskResult = ScriptResults.Success
End Sub

Upvotes: 0

Views: 4192

Answers (3)

Josh
Josh

Reputation: 2975

The problem is File.ReadAllText has limitations when it comes to reading a large file because the entire file is read into memory.

What you will need to do is replace the File.ReadAllText with reading the file line by line and append it accordingly.

EDITED FOR AN EXAMPLE:

Option Explicit
Dim oFSO, sFile, oFile, sText
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFile = "your text file"
If oFSO.FileExists(sFile) Then
    Set oFile = oFSO.OpenTextFile(sFile, 1)
    Do While Not oFile.AtEndOfStream
        sText = oFile.ReadLine
        If Trim(sText) <> "" Then
            fileContents.AppendLine(sText)
        End If
    Loop
    oFile.Close
Else
    WScript.Echo "The file was not there."
End If

It's possible you may still have an issue with the fileContents StringBuilder. The original error shown though was thrown from the File.ReadAllText method. So hopefully, this does the trick.

If not, I would just forget about the fileContents StringBuilder all together and write out the header. Then read from the file line by line and write it out line by line, then finally write the footer.

Upvotes: 1

Edmund Schweppe
Edmund Schweppe

Reputation: 5132

An alternative (and much more SSIS-like) solution would be to create a Data Flow Task that reads your existing file, pipes it through a Script Component that adds the header and footer, and writes it to the file system. Here's what it might look like in SSIS 2005:

enter image description here

The Script Component will be a Transformation with the SynchronousInputID of its output set to False, so that it can generate header and footer rows:

enter image description here

And the VB source of the transform should look something like this:

Public Class ScriptMain
    Inherits UserComponent
    Dim headerWritten As Boolean = False

    Public Overrides Sub IncomingRows_ProcessInputRow(ByVal Row As IncomingRowsBuffer)
        If Not headerWritten Then
            WriteHeader()
        End If
        OutgoingRowsBuffer.AddRow()
        OutgoingRowsBuffer.theLine = Row.theLine 
    End Sub

    Public Overrides Sub FinishOutputs()
        MyBase.FinishOutputs()
        WriteFooter()
    End Sub

    Private Sub WriteHeader()
        OutgoingRowsBuffer.AddRow()
        OutgoingRowsBuffer.theLine = "The First Header Line"
        headerWritten = True
    End Sub

    Private Sub WriteFooter()
        OutgoingRowsBuffer.AddRow()
        OutgoingRowsBuffer.theLine = "Here's a footer line"
        OutgoingRowsBuffer.AddRow()
        OutgoingRowsBuffer.theLine = "Here's another one"
    End Sub
End Class

This lets you use the streaming capabilities of SSIS to your advantage.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500815

Well, one simple way would be to just avoid the StringBuilder: open a TextWriter with File.CreateText, write the first line, then write File.ReadAllText(...), then write the final line.

However, that will only save you some memory - it will roughly halve the memory required, as you won't need it in both the StringBuilder and a string (which is what I think will happen now).

A much better alternative would be to:

  • Open the writer
  • Write the header line
  • Open the other file for reading
    • Loop over the file, reading a chunk of characters at a time and writing it to the new file, until you're done
    • Close the other file implicitly (use a Using statement for this)
  • Write the trailing line
  • Close the write implicitly (use a Using statement)

That way even if you've got huge files, you only need a small chunk of data in memory at a time.

Upvotes: 3

Related Questions