1179
Chapter 52: Building, Deploying, and Managing ETL Workfl ows in Integration Services
52
Destination Defi nitionExcel Sends rows from the data fl ow to a sheet or range in a workbook using an Excel
connection manager.
Strings are required to be Unicode, so any DT_STR types need to be converted
to DT_WSTR before reaching the Excel destination.
Flat File Writes the data fl ow to a fi le specifi ed by a Flat File connection manager.
Choose whether to overwrite any existing fi le and provide fi le header text if
wanted.
Ole DB Writes rows to a table, view, or SQL command (ad hoc view) for which an OLE
DB driver exists. Table/view names can be selected directly in the destination
or read from a string variable, and each can be selected with or without fast
load.
Fast load can decrease run time by an order of magnitude or more depending
on the particular data set and selected options.
Partition
ProcessingEnables the population of Analysis Services partitions without fi rst populating
the underlying relational data source.
Raw File Writes rows from the data fl ow to an Integration Services format suitable for
fast loads by a raw source component. It does not use a connection manager;
instead, specify the AccessMode by choosing to supply a fi lename via direct
input or a string variable.
Useful for increasing performance.
Recordset Writes the data fl ow to a variable. Stored as a recordset, the object variable is
suitable for use as the source of a Foreach loop or other processing within the
package.
Script A script can also be used as a destination. Use a script as a destination to for-
mat output in a manner not allowed by one of the standard destinations.
For example, a fi le suitable for input to a COBOL program could be generated
from a standard data fl ow.
SQL Server This destination uses the same fast-loading mechanism as the Bulk Insert task
but is restricted in that the package must execute on the SQL Server that con-
tains the target table/view.
Speed can exceed OLE DB fast loading in some circumstances.
SQL Server
CompactWrites rows from the data fl ow into a SQL Mobile database table.Understanding SSIS Transformations
Between the source and the destination, transformations provide functionality to change
the data from what was read into what is needed. Each transformation requires one or
more data fl ows as input and provides one or more data fl ows as output. Like sources andc52.indd 1179c52.indd 1179 7/31/2012 10:29:29 AM7/31/2012 10:29:29 AM
http://www.it-ebooks.info