Tuesday, September 21, 2010

SSIS Script Task to Find and Replace File contents

In my previous article Script Task to Read File I explained how to read the contents of a file and store in a package variable.
Few folks from my followers had been asked about Find and Replace mechanism using Script Task. In this article I am going to explain how to replace contents of a file.

I'll show following operations in this solution:
  1. Read contents of .sql file and store in a variable.
  2. Modify the WHERE condition of T-SQL query to fetch current months data
  3. Store the contents back to the file.
Here you go:

STEP1: Create a new ssis package and add two package variable as shown below:

STEP2: Drag and drop Script Task and rename it as Script Task - Read File and then Find and Replace String. Double click on Script Task to open Script Task Editor, Select User::FilePath as ReadOnlyVariables and User::FileContent as ReadWrite Variables. Now Click on Edit Script... and copy & paste below code:

public void Main()
{
  String ErrInfo = "";
  String FilePath = Dts.Variables["User::FilePath"].Value.ToString();
  try
  {
    String FileContent; //Variable to store File Contents
    FileContent = 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;
       return;
    }

    DateTime currDate, StartDate, EndDate;
    currDate = DateTime.Today;
    StartDate = currDate.AddDays(-(currDate.Day - 1));
    EndDate = StartDate.AddMonths(1).AddDays(-1);

    //FileContent Before Replace;
    MessageBox.Show(FileContent);

    //Find and Replace --> Modify WHERE clause
    FileContent = FileContent.Replace(
       "D.DateKey BETWEEN 20080101 AND 20080131",
       "D.DateKey BETWEEN " +
       String.Format("{0:yyyyMMdd}", StartDate) + " AND " +
       String.Format("{0:yyyyMMdd}", EndDate)
    );

    //FileContent After Replace;
    MessageBox.Show(FileContent);
    Dts.Variables["User::FileContent"].Value = FileContent;

    //Write the contents back to File
    WriteToFile(FilePath,FileContent,ErrInfo);
    if (ErrInfo.Length > 0)
    {
       Dts.Log("Error while writing File " + FilePath, 0, null);
       Dts.Log(ErrInfo, 0, null);
       Dts.TaskResult = (int)ScriptResults.Failure;
       return;
    }
  }
  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 "";
  }
}

public void WriteToFile(String FilePath, String strContents, String ErrInfo)
{
  StreamWriter sWriter;
  try
  {
    sWriter = new StreamWriter(FilePath);
    sWriter.Write(strContents);
    sWriter.Close();
  }
  catch (Exception e)
  {
    MessageBox.Show(ErrInfo);
    ErrInfo = e.Message;
  }
}

STEP3: We are done with package changes. Execute the package to test the output.

Messagebox for actual file contents

Messagebox after REPLACE (modified WHERE clause)

Now if you look at the actual file, the query has been modified as shown below:


Cheers!!!

9 comments:

  1. StreamReader and StreamWriter cannot be found so my script has errors. Please help

    ReplyDelete
  2. Include the below line on top

    using System.IO;

    ReplyDelete
    Replies
    1. Great. This script works. One question I have is what about multiple character replacement. Can we do that with this script?

      Delete
  3. This is flawless. Thank you very much. You saved a lot of time for me.

    ReplyDelete
  4. script result does not exist in the current context, please help

    ReplyDelete
  5. Hi,

    I need to do 4 replaces in a file...how can I do this ? I tried to add more replace commands but it is not working. Thank you

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. awesome, you have saved me lot of time dude.

    ReplyDelete
  8. Works for me after fixing StreamReader and StreamWriter, thank you so much!

    ReplyDelete

Here are few FREE resources you may find helpful.