Thursday, September 16, 2010

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

10 comments:

  1. using your VB example I get a ReadFile not declared error.

    ReplyDelete
  2. Hi Rick,
    before posting my articles, I developed and test. my package is working fine for both C# & Visual Basic.
    Also ReadFile is Function not a variable. Could you tell me exact error you got?

    ReplyDelete
  3. this is good
    but can u send me a easy method of writing script for message box

    ReplyDelete
  4. While writing an code for an msg box there is an red mark on the task that is show in an error as this task is compiled to pre defined script.can u expalin me ..

    ReplyDelete
  5. To display a message, use:
    MessageBox.Show("AnyString")

    ReplyDelete
  6. sorry sir, what i ment is that there is an import statement that i need it

    ReplyDelete
  7. Hi Hari Sir,

    This is good Example .But Its open only txt file rather then excel or word file.

    ReplyDelete
  8. Hi Hari,

    very nice post,could you please send me the package on naga272009@gmail.com

    Thanks,
    Nagababu

    ReplyDelete
  9. Very nice! Exactly what I needed to get around the 8000 character issue that SSIS has with some of it's tasks. And I only had to change the variable names and comment out the message box statements.

    Thank you very much,
    Kenney

    ReplyDelete

Here are few FREE resources you may find helpful.