Processing a Flat File with Header and Detail Rows

It’s been tough finding time to write any content recently, but this was a scenario that came up recently both at work and on the forums, so I decided to write it up. It’s another variation on processing flat files (they seem to be endless). This scenario deals with an input file that contains two types of records, headers and details. This processing for this is based off another post (Handling Flat Files with a Varying Number of Columns), so I’m only going to cover what is unique in this scenario.


The input file looks like this:


HL111Header1
DLDetail1
DLDetail2
HL222Header2
DLDetail3
DLDetail4

The rows that begin with HL are header lines, the rows with DL are detail lines. One additional requirement in this example is that the header code (the three digits following the HL in each header line) must be included into each detail row.


The data flow looks like this:


HeaderDetail Data Flow


The Flat File Source is bringing in each row as a single column, delimited by CR/LF. The script component has two outputs defined, both as synchronous, and with an exclusion group greater than zero. (For more info on this, please see this post from Jamie Thompson.) One output will include the Header row, and the other will include the Detail rows. Note that each output must have unique column names defined.


The script appears below. It is simply parsing the Line column (that contains the entire row of data), and putting the values into the appropriate columns on each output. Note that the headerCode variable is stored each time a header row is encountered, and then used to populate the code value on the detail output. 


Public Class ScriptMain
Inherits UserComponent
Private headerCode As Integer

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
If Row.Line.Substring(0, 2) = “HL” Then
headerCode = CType(Row.Line.Substring(2, 3), Integer)
Row.DirectRowToHeader()
Row.HRRecordType = Row.Line.Substring(0, 2)
Row.HRCode = CType(Row.Line.Substring(2, 3), Integer)
Row.HRName = Row.Line.Substring(5)
ElseIf Row.Line.Substring(0, 2) = “DL” Then
Row.DirectRowToDetail()
Row.RecordType = Row.Line.Substring(0, 2)
Row.Code = headerCode
Row.Name = Row.Line.Substring(2)
End If
End Sub

End Class


There it is. Fairly straightforward, but it was quick to put together. The sample files are attached below.

11 Comments

  1. toddmcdermid says:

    Pointed someone here from the MSDN forums. They thought they couldn’t use your technique because the sample contained the Trash destination. Just wanted to say that the Trash destination (a third party component) is not necessary to use this technique – the meat and potatoes are the script contents.

  2. barbara says:

    Great, I could use this because I have a file with
    header0000000
    the detail data here (no DL or Detail before the data)
    Trler
    header000000
    detail data here (no DL or prefix before the data)
    trler

    How can I modify your script to get only detail information? I am new to ssis – much appreciate.
    bar0822

  3. Keith Langley says:

    How about if you need to attach the ‘HL’ to the ‘DL’ records.

    For example I have 29 different headers types and I need to know how to process the details by what type of header I have. So if I could add 01-29 to the DL records I can use a conditional split to separate and process.

    sample data…

    hl01
    dl
    dl
    dl
    hl2
    dl
    dl
    dl
    dl
    hl03
    dl
    dl
    dl

    etc

  4. Keith Langley says:

    Nevermind, that is your solution. Thanks

  5. Waldo van Graan says:

    When I use the script it keeps asking me “property access must assign to the property or value” – Am I missing something?

  6. Nicolas says:

    hi,

    thanks for sharing the idea.

    just as an alternative –

    you can use a ‘derived column’ to populate a new column that defines where i row should be redirected to
    followed by a ‘conditional split’ to create dataflows with the rows directed into different outputs.

    Nicolas

  7. Hari says:

    I need to do the exact opposite. I need to create this text file from the two database tables. Also included in the text file is one trailer records that summarize one particular filed in all the headers and count the total number of details for all headers. Can someone please help me ASAP. Thank You

Leave a Reply to toddmcdermid