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.

Imports System.IO

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.

Imports System.IO

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.

CONCLUSION

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.