Adding Headers and Footers to Flat Files

A common question with SSIS is how to handle adding headers and footers to a flat file when the format is different that the data rows. This gets more difficult when the header or footer needs to contain data based on the actual data rows, such as a total or a record count.


Basically, this can be accomplished by creating the header or footer in a separate task, and then prefixing or appending it to the data. I’ve prepared a sample package that shows a couple of different ways to accomplish this. For generating footers, it shows how to use a second data flow to append an additional row onto the footer. There are two methods shown for adding headers. Method 1 uses a Script task to add the header by loading the file into a string, adding the header, and saving the file again. Method 2 creates a separate header file, and uses a file copy operation to concatenate the two files.


image


There a couple of things to note about the package. There are three flat file connection managers defined in the package.


image


Two of the connection managers (Destination and DestinationWithFooter) point to the same file, but have different column definitions. All three have their connection strings set by expressions that point to variables. If you want to change the target files, you need to modify the variables, not the connection managers.image


The DestinationFile variables holds the path to a working file, which will hold the data before the headers and footers are added. The DestinationHeader variable holds the path to a working file used only for method 2, which holds the header row before it is concatenated into the final file. The FinalFile variable holds the path to the final output file, which includes the header, data, and footer rows.


The Generate File data flow uses a Data Generator component from Konesans (a free download from www.sqlis.com). It simply generates some data, captures the number of rows using a Row Count transform, and outputs it to the Destination connection manager.


The Append Footer and Create a Header File data flows are very similar. They both use a Script source to generate a single row containing the footer or header data, respectively. The script is below.



Public Overrides Sub CreateNewOutputRows()
    Dim recordCount As Integer
    Dim vars As IDTSVariables90
 
    ‘Get the record count
    Me.VariableDispenser.LockOneForRead(“RecordCount”, vars)
    recordCount = CType(vars(“RecordCount”).Value, Integer)
    vars.Unlock()
 
    ‘Output one row with record count
    Output0Buffer.AddRow()
    Output0Buffer.FooterRow = String.Format(“Footer Row Count: {0}”, recordCount)
    Output0Buffer.SetEndOfRowset()
End Sub

The two differ in the way the destination is handled. In the Append Footer data flow, the Flat File destination is set to append to the file, rather than overwriting it. It uses the DestinationWithFooter connection manager, which points to the same file that the data was sent to in the Generate File data flow. The DestinationWithFooter connection manager defines the file as having a single column. That makes it easier to append the footer row directly to the file. After the Append Footer data flow task has run, there is a single file that has the data rows and a footer row.


image


In the Create a Header File data flow, the header row is sent to a new file. Then, in the Append Files execute process task, the header and the data file are concatenated together using a copy command. This is Method 2 for adding the header.


For Method 1, the Add Header script tasks uses the System.IO.File class and the System.Text.StringBuilder class to create a new file containing the header row and the data rows, which already has the footer included.



Public Sub Main()
    Dim fileContents As New StringBuilder()
    Dim vars As Variables
    Dim recordCount As Integer
    Dim finalFile As String
 
    ‘Get the record count
    Dts.VariableDispenser.LockForRead(“RecordCount”)
    Dts.VariableDispenser.LockForRead(“FinalFile”)
    Dts.VariableDispenser.GetVariables(vars)
    recordCount = CType(vars(“RecordCount”).Value, Integer)
    finalFile = CType(vars(“FinalFile”).Value, String)
    vars.Unlock()
 
    ‘Write header, then append file contents and write back out.
    fileContents.AppendLine(String.Format(“Header Row Count 1: {0}”, recordCount))
    fileContents.Append(File.ReadAllText(Dts.Connections(“Destination”).ConnectionString))
    File.WriteAllText(finalFile, fileContents.ToString())
 
    Dts.TaskResult = Dts.Results.Success
End Sub

Hopefully this example is helpful, if you are trying to sort through the options for adding headers and footers. The example is available on my Skydrive here.

6 Comments

  1. Murray Richburg says:

    Good article but images aren’t showing for me.

  2. Chris Page says:

    Our work servers blocked your site so I had to figure out another solution – I created 3 files, 1 for each of the header, body and footer and then used and execute process task to use a dos prompt to cocantenate one file to the other (cmd /c xxx.txt >> yyy.txt) – worked nicely and has kept me away from scripts just a little longer!

    • Greg T says:

      This approach is great! Creating separate files and then Chris Page’s approach of concatenating them with a file system command (ie. copy header.txt+body.txt results in the concatenated file in header.txt). Very simple.
      (And one day when I’m big, I want to use Linux and just use a CAT command to join files).

  3. M.Saravanan says:

    Hi nice article. I have been working with SSIS to create a flat file with Header,Body and footer with the following logic, creating three outputs (Header,Data and Footer) in the script component (Inside DFT). Each output will have required columns in it with an additional column called RecType. Inside the script component i will hardcode the RecType column with “-1″ for Header output, “0″ for Data Output and “9999″ for Trailer output. All these three output will be sent to union all and then sort it by RecType so that Header output will be the first record, followed by Data record and finally trailer record. So i guess we can avoid creating multiple files in our package.

    • jwelch says:

      Yes, the script approach works as well, though you have to manage all the logic in code. Not necessarily a bad thing (I’ve done that myself in some cases), but some people prefer using the non-code approach as much as possible.

Leave a Reply