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

Beyond flat files, staging supports a key tenet of the Extraction phase of any ETL
solution: impact the source system-of-record as little as possible. Often, an important
business driver for building an ETL solution in the first place is the difficulty of query-
ing data in the system-of-record for reporting purposes. ETL's first goal is similar to
that of the Hippocratic Oath: “ Primum non nocere ” (First, do no harm).
Staging requirements for some ETL lend themselves to storing a copy of all source
data, whether from flat files or not. Other requirements allow for applying some trans-
formative logic prior to staging. Which is the correct answer? “It depends.” In my
opinion, the very act of copying data from a text source and landing it in a relational
database represents a transformation.
This, then, becomes a pattern of staging data: copying data straight from a flat file
into a database. To that end, let's complete the example we've started.
Drag an OLE DB Destination adapter onto the data flow canvas and connect a data
flow path from the Derived Column transformation to the OLE DB destination. Before
you proceed, double-click on the data flow path to open its editor, and then click on the
Metadata page. You'll see something that looks like Figure 7-5 .