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!!!