Flat File Source Patterns - SQL Server Integration Services Design Patterns - page 196

Figure 7-14 . Happiness is green checks
This isn't the only way to address loading files of this format. It is one way, and it
has the advantage of offering a lot of flexibility.
Header and Footer Rows
Header and footer rows are included in extract files from many sources. I regularly see
these rows in flat files delivered from mainframe-based database systems.
A header row contains metadata about the contents of the extract file—a summary
of information of the extract. At a minimum, it will include the extract date. Usually a
field contains a row count for the extract. When you think about it, this is very practical
instrumentation—it provides a way to check for the proper number of rows. This check
can be performed immediately after the extract or immediately after loading the ex-
tract—both are valid uses of this metadata.
A footer row is identical in concept. The only difference is location: header rows
appear first in a file; footer rows appear last. If you're including row counts to validate
that the correct number of rows have been written or read, writing this information first
is a good way to increase fault tolerance. Why? Imagine a failure: the write operation is
interrupted or the extract ends abnormally. The header row may indicate 100 rows, for
example, but only 70 rows of data follow. If the row count metadata is included in the
header row, it's possible to calculate exactly how many data rows are missing. In con-
trast, a footer row would simply be missing. Although a missing footer would indicate
that the extract had failed, that's all it would indicate. Having the row count metadata
present would allow you to glean more and better information regarding the failure.