Monday 16 February 2015

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

5 comments:

  1. You are such a life saver... 1000 of post I have seen for adding footer.. No one mentioned such straight solution for big issue.. Your rock

    ReplyDelete
  2. What a straight forward solution , big ups man

    ReplyDelete
  3. Great article, works like a charm, thanks!

    ReplyDelete
  4. i get the below error when running the script, no idea what i am doing wrong

    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
  5. I cannot thank you enough! Paramjeet said it best, it was the correct solution of all 1000 posts.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...