The Table or view - fast load access mode is one of five data access modes available in the OLE DB Destination Editor of a Data Flow object. The other access modes are
- table or view
- table or view name variable
- table or view name variable – fast load
- SQL command
It would be nearly impossible to list every permutation of when you would use each of these options, but you should be aware of the pros and cons of using the fast load option. Fast loads, whether from named tables or variables, give you much finer control over your data inserts. The four biggest options appear as check boxes in the Destination Editor. You can
- choose to keep source system identities
- keep null values (but make sure they’re true null values, not strings containing the word null)
- lock the table
- check constraints
You have no control over these options with a table load because the load will default to whatever rules are set for the database. So, for example, constraints will always be checked on a table load, whereas you can turn this feature off with a fast load.
Fast loads are optimized for bulk inserts, so you can specify the number of rows per batch and maximum insert commit size. With batch inserts, the failure of any one row to insert will cause every row in the batch to fail, so you can limit the damage in the event of an error and get most of the relevant data into the target table. Make sure you handle any resulting errors (e.g., by logging them).
Main article: Transforming Data Using SSIS and SQL
