When working with SSIS, you’ll often find the need to read the contents of a flat file to a variable, and write the value of a variable to a flat file. A script task can be used to accomplish both of these.
READ CONTENTS OF A FLAT FILE TO A VARIABLE
I’ve saved a flat file called MyFile.txt to my local drive. Then in the SSIS package, I created a new variable called sFileContent and specified it as a ReadOnlyVariable. Since we’ll be accessing files in a directory, we need to import the System.IO namespace.
The following script initializes a StreamReader Class, reads the content of the file from the specified path as string, and assigns that string to the variable.
Public Sub Main() 'initialize StreamReader class for text file Dim streamReader As New StreamReader("C:\Demo\MyFile.txt") ' Read the StreamReader To End and assign to local variable Dim StreamText As String = streamReader.ReadToEnd() ' assign SSIS variable with value of StreamText local variable. Me.Dts.Variables("sFileContent").Value = StreamText Dts.TaskResult = ScriptResults.Success End Sub
To test this, I added another script and used message box to prompt the value of the variable. Off course, sMyVariable is specified as a ReadOnlyVariable for this script task as well.
Public Sub Main() MsgBox(Dts.Variables("sFileContent").Value.ToString) Dts.TaskResult = ScriptResults.Success End Sub
The content of the flat file is displayed when I run the package.
WRITE VALUE OF A VARIABLE TO A FLAT FILE
I created another SSIS package and created a variable called sMyVariabe. A value is assigned to this variable. Again in the script task, specify sMyVariable as a ReadOnlyVariable since we’re going to read it in the script task and make sure to import System.IO namespace.
Use the following script which uses StreamWriter class to write the value of the specified variable to the file specified in the script.
Public Sub Main() ' write DTS variable to a file using stream writer Using sw As StreamWriter = New StreamWriter("C:\Demo\PkgVariable.txt", True) sw.WriteLine(Dts.Variables("sMyVariable").Value.ToString) End Using Dts.TaskResult = ScriptResults.Success End Sub
when I run the package, I see a flat file with the message from the package variable.
In this simple example, you saw two things using script task: 1) read the contents of a flat file to a variable and 2) write the value of a variable to a flat file. I’ve passed a static file path for simplicity. You can however parameterize the paths to make it work in a more dynamic fashion.