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

In this introductory section, I've introduced concepts of staging and we built a pat-
tern to stage data from a flat file into a database table. Along the way, we delved into
some data warehousing thinking and peeked under the hood of the Data Flow task.
Next up: loading another format of flat file—one with variable-length rows.
Variable-Length Rows
A variable-length row flat file is a text source file. It can be a comma-separated values
(CSV) file or a tab-delimited file (TDF). It can be a fixed-length file where columns
are identified positionally or by ordinal. The major difference between a “normal” flat
file and a variable-length row flat file is that the number of text positions is fixed in a
normal flat file, and that number can change with each row in a variable-length flat file.
Let's look at an example of a variable-length flat file:
RecordType,Name1,Value1,Name2,Value2,Name3,Value3
A,Multi One,11
B,Multi Two,22,Multi Two A,23
C,Multi Three,33,Multi Three A,34,Multi Three B,345
A,Multi Four,44
C,Multi Five,55,Multi Five A,56,Multi Five B,567
B,Multi Six,66,Multi Six A,67
There are seven potential columns: RecordType , Name1 , Value1 , Name2 ,
Value2 , Name3 , and Value3 . Not all rows contain seven values. In fact, the first
row contains only three values:
A, Multi One,11
In this format, the RecordType is in the first column and this indicates how many
columns of data to expect in the row. Rows of RecordType A contain three values,
rows of RecordType B contain five values, and those of RecordType C contain
seven values.
Reading into a Data Flow