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

Question

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
objFile.Close()
strNewText = Replace(strText, "Jim", "James")

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

Matt · Accepted Answer · 2016-09-14 17:09:51Z

2

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"))

answered Sep 14, 2016 at 17:09

Matt

14.4k2 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2016-09-14 17:14:03Z

2

You can pass your customer variables to the [task script] in tab Script using ReadOnlyVariables and/or ReadWriteVariables, after having defined the variables globally; take a look here

More in detail:
to add variables to your project you can select the [Variables] tab in the upper left in the Package, than insert requirend fields:

Variables can be added to the [task script] by selecting them as ReadOnlyVariables and/or ReadWriteVariables:

so you can use within the [task script] by declaring:
Dts.Variables("User::Variable").Value

as @Matt says online documentation omits 'User::'

edited Sep 14, 2016 at 17:14

Matt

14.4k2 gold badges19 silver badges29 bronze badges

answered Sep 14, 2016 at 14:46

ɐlǝx

1,3922 gold badges17 silver badges23 bronze badges

3 Comments

Matt Over a year ago

I would only note that I have had issues when I don't use the "USER::" identifier e.g. Dts.Variables("User::VariableName").Value method which I know is weird because of Microsoft's documentation say you shouldn't have to use it.... @M.Mehta getting an error doesn't give Alex or the community any details of what could possible be wrong you should update your post with the details of your current attempt and the error.

M. Mehta Over a year ago

Matt, I don't get you exactly what you are trying to say. I found few VB scripts that written with User variables but not for replace text in file of course. So I am trying to figure out how this script will be work if I want to use Use variables instead of hard coded path!!

M. Mehta Over a year ago

Thanks to Alex and Matt.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related