2

I'm using the below code inside a ssis script task to modify the contents of a file. I'm basicallly creating 1 json document when in the file there are many jsons, one after the other. This code works perfectly up until around a 1GB file (to read the 1GB file it's using almost 7GB memory in SSIS), after that it crashes (i assume due to memory). I need to read files up until 5GB.

Any help please

   Public Sub Main()

    Dim filePath As String = Dts.Variables("User::filepath").Value.ToString()

    Dim content As String = File.ReadAllText(filePath).Replace("}", "},")
    content = content.Substring(0, Len(content) - 1)
    content = "{ ""query"" : [" + content + "] }"
    File.WriteAllText(filePath, content)
    Dts.TaskResult = ScriptResults.Success
End Sub
Hadi
  • 36,233
  • 13
  • 65
  • 124
DC07
  • 293
  • 5
  • 18

1 Answers1

1

It is not recommended to use File.ReadAllText(filePath) to read big flat files because it will store all the content in memory. I think you should use a simple data flow task to transfer the data from this flat file to a new flat file, and you can do the transformation you need in a script component on each row.

Also you can read it line by line in a script using a StreamReader using and write it to a new file using a StreamWriter, when finished you can delete the first file, and rename the new one.

References

Hadi
  • 36,233
  • 13
  • 65
  • 124