Database Reference
In-Depth Information
CHAPTER 7
Flat File Source Patterns
A common way to transfer data between systems is to export the source data to a flat file
and then import the contents of this file into the destination database. Flat files come in
all shapes, sizes, and types. There are no row-length limitations. File size is limited by
the maximum size allowed by the operating system. When examining flat file types,
there are two initial considerations: file format and schema. Common file formats of flat
file sources include these:
• Comma-separated values (CSV)
• Tab-delimited file (TDF)
• Fixed-width file
In a flat file, as in a database, schema includes columns and data types. Schema op-
tions also allow for more exotic file format options such as “ragged right” and “variable-
length rows” flat files.
In this chapter, we'll examine a common pattern for loading a vanilla flat file source
into SQL Server; then we'll expand that pattern to load a variable-length row flat file
source. We will next examine creating and consuming flat file header rows, which are
found in some flat file formats. Finally, we will construct an extremely useful SSIS
design pattern: Archive File.
Flat File Sources
Search WWH ::
Custom Search