Script Task to Read File

Problem: Reading a file using Script Task

Solution: I'll explain this by creating a new SSIS package, reading the contents of the file and store the results in a package variable using Script Task.
In this article, I have covered about how to Read File in Script using Visual Basic 2008 and Visual C#  2008. Below are the steps to achieve the goal:

STEP1: Create a new SSIS package (I have renamed it as ReadFile.dtsx) and add two package variables FilePath and FileContent as shown below:

FilePath is used to store the path of the file being read by script task. FileContent variable isa used to store the contents of the file.

STEP2: Drag & drop Script Task and double click to open Script Task Editor. Enter Script Task - Read File using C Sharp 2008 as Name in General tab. Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWriteVariables, and Microsoft Visual C# 2008 in ScriptLanguage in Script tab.

Note: Here I am using two script tasks to explain the solution in both Visual Basic and C# 2008.

STEP3: Click on Edit Script... button of Script Task Editor and paste following code:

using System.IO;
public void Main()
{
  String ErrInfo = "";
  String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
  try
  {
    Dts.Variables["User::FileContent"].Value = ReadFile(FilePath, ErrInfo);
    if (ErrInfo.Length > 0)
    {
      Dts.Log("Error while reading File " + FilePath, 0, null);
      Dts.Log(ErrInfo, 0, null);
      Dts.TaskResult = (int)ScriptResults.Failure;
    }
    else
    {
      MessageBox.Show(Dts.Variables["User::FileContent"].Value.ToString());
      //Here do whatever you want to do with file contents
      Dts.TaskResult = (int)ScriptResults.Success;
    }
  }
  catch (Exception e)
  {
    Dts.Log(e.Message, 0, null);
    Dts.TaskResult = (int)ScriptResults.Failure;
  }
}


public String ReadFile(String FilePath, String ErrInfo)
{
  String strContents;
  StreamReader sReader;
  try
  {
    sReader = File.OpenText(FilePath);
    strContents = sReader.ReadToEnd();
    sReader.Close();
    return strContents;
  }
  catch (Exception e)
  {
    MessageBox.Show(ErrInfo);
    ErrInfo = e.Message;
    return "";
  }
}

STEP4: Thats all. We are done with package development. The content of File will be stored in the package variable FileContent while executing the package. To test the output, execute this package, a message box will popup (I didn't comment the Messagebox in the code) as shown in below:

And here is the snapshot of actual file:

Below is the code for Visual Basic 2008.

Imports System.IO

Imports System.Text
 
Public Sub Main()
  Dim dataBytes() As Byte = Nothing
  Dim ErrInfo As String = "" 'Variable to store Error Information
  Try
    Dim FilePath As String = Dts.Variables("User::FilePath").Value.ToString
    Dts.Variables("User::FileContent").Value = ReadFile(FilePath, _
     ErrInfo).ToString()
    If ErrInfo.Length > 0 Then
      Dts.Log("Error while reading contents of file " & _
      FilePath, 0, dataBytes)
      Dts.Log(ErrInfo, 0, dataBytes)
      Dts.TaskResult = ScriptResults.Failure
      Exit Sub
    End If
    MessageBox.Show(Dts.Variables("User::FileContent").Value.ToString)
    Dts.TaskResult = ScriptResults.Success
  Catch Ex As Exception
    Dts.Log(Ex.Message, 0, dataBytes)
    Dts.TaskResult = ScriptResults.Failure
    Exit Sub
  End Try
End Sub


Public Function ReadFile(ByVal FilePath As String, _
Optional ByVal ErrorInfo As String = "") As StringBuilder
  Dim strContents As New StringBuilder
  Dim objReader As StreamReader
  Try
    objReader = New StreamReader(FilePath)
    strContents.Append(objReader.ReadToEnd())
    objReader.Close()
    Return strContents
  Catch Ex As Exception
    ErrorInfo = Ex.Message
    Return strContents
    Exit Function
  End Try
End Function