Read contents of a file to variable – SSIS

Sometimes you will find the need to import a file to a variable using SSIS. In this post, I’ll use Script Task to read contents of a flat file into a variable.

I’ve created a file called ‘myfile.txt’ with some random text. I’ll configure script task to read this file into a SSIS variable.

  • First create a variable with string data type. I’ll call mine vText.
  • Drag a script task onto the control flow. As you would imagine double clicking the script task will open the editor.
  • Specify the variable you just created as a ReadWriteVariable.
  • Click edit script and use code similar to what you see below:

'initialize StreamReader class for text file
Dim streamReader As New StreamReader("D:\Documents\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("vText").Value = StreamText

To test, I’ll add another script task. This task will show a message box with the contents of our variable. Following is the script i use for message box:

MsgBox(Dts.Variables("vText").Value.ToString)

As you can see I’m reading the file from a static location on my D drive. Most people, however, will use variables or expressions to read from any locations dynamically.

@SamuelVanga.

About these ads

7 thoughts on “Read contents of a file to variable – SSIS

  1. Pingback: Write a Variable To a File Using SSIS « Sam Vanga's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s