M. Mehta M. Mehta - 1 year ago 154
Vb.net Question

SSIS - Script task: Replace TEXT in text file using VBscript with dynamic file path

I am using SSIS script task to replace text in text file. In my VB script hard coded file path in script but I want to use user variable instead.

Const ForReading = 1
Const ForWriting = 2
Dim objFSO, objFile, strText, strNewText

objFSO = CreateObject("Scripting.FileSystemObject")
objFile = objFSO.OpenTextFile("C:\Users\newtons\Desktop\Text.txt", ForReading)

strText = objFile.ReadAll
strNewText = Replace(strText, "Jim", "James")

objFile = objFSO.OpenTextFile("C:\Users\newtons\Desktop\Text.txt", ForWriting)
objFSO = Nothing
objFile = Nothing

Answer Source

Use @Alex's answer to add and reference variables. But just an FYI that you can do your entire script in 2 lines of code if you use System.IO.

so add this to the imports section

Import System.IO

Then use these lines in your MAIN Sub

Dim filePath As String = Dts.Variables("User::FullFilePath").Value.ToString()
File.WriteAllText(filePath, File.ReadAllText(filePath).Replace("Jim", "James"))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download