SSIS脚本任务（VB）读取大文件内存溢出问题求助

Hey there, I see you're hitting a classic memory bottleneck with your SSIS script task—reading the entire 5GB file into memory at once is definitely going to cause crashes. Let's break down why your current code is struggling, then fix it with a streaming approach that keeps memory usage low no matter how big the file gets.

Why Your Current Code Fails #

Your current code uses File.ReadAllText() which loads the entire file into a single string. In .NET, strings are stored as UTF-16 (2 bytes per character), so a 1GB file becomes ~2GB in memory right off the bat. Add in the intermediate strings from .Replace() and concatenation, and you end up with that 7GB memory footprint. For 5GB files, this would balloon to 10GB+—way more than most systems can handle without crashing.

Solution: Stream the File (No Full Load into Memory) #

Instead of loading the entire file at once, we'll read it line-by-line (or in chunks) and write to an output file incrementally. This keeps memory usage minimal because we only hold a small portion of the file in memory at any time.

Here's the revised VB.NET code for your SSIS script task:

Imports System.IO

Public Sub Main()
    Dim inputFilePath As String = Dts.Variables("User::filepath").Value.ToString()
    ' Use a temporary file to avoid corrupting the original if something goes wrong
    Dim tempFilePath As String = Path.Combine(Path.GetDirectoryName(inputFilePath), "temp_merged.json")
    
    Dim isFirstObject As Boolean = True
    
    Try
        Using reader As New StreamReader(inputFilePath)
            Using writer As New StreamWriter(tempFilePath, False, System.Text.Encoding.UTF8)
                ' Write the opening of our merged JSON
                writer.WriteLine("{ ""query"" : [")
                
                Dim line As String
                ' Read each line one at a time
                While reader.Peek() >= 0
                    line = reader.ReadLine()
                    ' Skip empty lines if any
                    If String.IsNullOrWhiteSpace(line) Then Continue While
                    
                    ' Clean up the line (remove any trailing whitespace/newlines)
                    line = line.Trim()
                    
                    ' Handle the first JSON object differently (no leading comma)
                    If isFirstObject Then
                        writer.WriteLine(line)
                        isFirstObject = False
                    Else
                        ' Add a comma before subsequent objects
                        writer.WriteLine("," & line)
                    End If
                End While
                
                ' Write the closing of our merged JSON
                writer.WriteLine(" ] }")
            End Using
        End Using
        
        ' Replace the original file with the merged temp file
        ' Optional: Backup the original file first if needed
        File.Delete(inputFilePath)
        File.Move(tempFilePath, inputFilePath)
        
        Dts.TaskResult = ScriptResults.Success
    Catch ex As Exception
        ' Clean up temp file if something fails
        If File.Exists(tempFilePath) Then File.Delete(tempFilePath)
        Dts.Events.FireError(0, "JSON Merge Task", ex.Message, String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure
    End Try
End Sub

Key Improvements in This Code #

• Streaming I/O: Uses StreamReader and StreamWriter to process the file in small chunks (one line at a time), so memory usage stays in the MB range even for 5GB files.
• Safe Handling: Uses a temporary file to avoid corrupting the original file if the task fails mid-process.
• Cleaner JSON Structure: Instead of adding commas to every line then removing the last one, we track the first object to avoid extra commas—this eliminates the risk of off-by-one errors if the file ends unexpectedly.
• Error Handling: Includes try/catch to clean up temp files and report errors to SSIS.

Notes for Edge Cases #

• If your JSON objects aren't on separate lines (they're concatenated without newlines), you'll need to adjust the reading logic to parse JSON objects incrementally. One approach is to read chunks of the file and use a regex to match complete JSON objects, then write them out with commas.
• Ensure the SSIS execution account has read/write permissions for the file directory.
• For very large files, you might want to increase the buffer size for StreamReader/StreamWriter—add a buffer size parameter (e.g., New StreamReader(inputFilePath, Encoding.UTF8, True, 65536) for 64KB buffers) to optimize performance.

内容的提问来源于stack exchange，提问作者DC07