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.

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

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.

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.

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.