Add footer to Flat File

Case
I have a requirement to add a footer to a flat file with data details, like row count and export date. How do I do that in SSIS?

Flat File with footer text

Solution
If you search the internet you will find several different solutions. Here is a solution with a Script Task.

1) Data Flow Task
I have a standard Data Flow Task with a Row Count Transformation to store the number of records in an integer variable named RowCount and a Flat File Destination to save the data in a textfile. The Connection Manager is named Employee.

DFT with Flat File Destination

2) Footer variable
To keep the .Net code simple and clear I will use an expression on an SSIS string variable to do all the 'difficult stuff'. Add a string variable named Footer and add an expression on it that suits your footer needs. You can make it as complex as you want. In this case a text with the rowcount in it and an export date in a certain format:
"This file contains " +  (DT_WSTR, 6)@[User::RowCount] + " records. Export date: " +
(DT_WSTR, 4)YEAR(GETDATE())  +
RIGHT("0" + (DT_WSTR, 2)MONTH(GETDATE()),2) +
RIGHT("0" + (DT_WSTR, 2)DAY(GETDATE()),2)

Expression with footer text

3) Script Task
Add a Script Task below your Data Flow Task and add the string variable as readonly variable.

Readonly variable: Footer

4) The Script
Add the following using (System.IO) and copy the content of my Main method to yours. It gets the location from your Flat File Connection Manager and appends the content of your footer variable to the bottom. Very basic code and no need to add more code other then error handling.

// C# Code
#region Namespaces
using System;
using System.Data;
using System.IO; // Added
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_719acd579f7e46adb5d68fb2fdd19625
{
  [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
  public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  {

    public void Main()
    {
      // Get ConnectionString from Connection Manager (case sensitive)
      string filePath = Dts.Connections["Employee"].AcquireConnection(Dts.Transaction).ToString();

      // Open the file from the connection manager to append some text
      using (StreamWriter sw = File.AppendText(filePath))
      {
        // Append text from string variable to file
        sw.WriteLine(Dts.Variables["User::Footer"].Value.ToString());
      }

      // Close Script Task with success
      Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    /// <summary>
    /// This enum provides a convenient shorthand within the scope of this class for setting the
    /// result of the script.
    /// 
    /// This code was generated automatically.
    /// </summary>
    enum ScriptResults
    {
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion

  }
}

5) The Result
Now run the package and check the result in the Flat File. Not that variable and connection manager names are case sensitive in the script.

Add Footer Script Task