Database Reference
In-Depth Information
Figure 8-15.
Selecting a data access mode
To select your data connection, use the “OLE DB connection manager” dropdown box, shown in Figure
8-15
, and select the OLE DB connection object that has already been created in the SSIS package. If, for some
reason, you have not created an OLE DB connection before you get to this point, clicking the New button allows
you to create one.
After you select your connection, configure the data access mode you want by using the “Data access mode”
dropdown box (Figure
8-15
). An OLE DB destination has five data access modes to choose from:
•
Table or view
: Insert values into a new or existing table or view.
•
Table or view - fast load
: Bulk insert into a new or existing table or view.
•
Table name or view name variable
: Same as the “Table or view” option, but you pass the
table or view name through an SSIS variable
•
Table name or view name variable - fast load
: Same as the “Table or view - fast load”
option, but you pass the table or view name through a SSIS variable.
•
SQL command
: You use a SQL query to describe the table and column names.
You will use the “Table or view - fast load” option for most occasions, because it provides additional
configuration options as well as it is easy to use.
■
Fast load uses sQl server bulk insert statements. The other option uses regular sQl server insert
statements. since sQl server 2005 and later, BulK insERT enforces new, stricter data validation. The two
most common issues occur when unicode text data is not used or when the decimal data type is not used for
floating-point numbers. You can find more information in sQl Books online, but since we converted all of our
examples to unicode and decimal data types, this should not be an issue for our exercises.
Tip
