70-463 · Question #65
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A . The package includes a data flow and is executed on ServerB. The destination tabl
The correct answer is A. OLE DB Destination with Fast Load. OLE DB Destination with Fast Load is the only destination that supports KEEPIDENTITY, configurable batch sizes, and remote server connections simultaneously.
Question
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on Server A . The package includes a data flow and is executed on ServerB. The destination table has its own identity column. The destination data load has the following requirements:
- The identity values from the source table must be used.
- Default constraints on the destination table must be ignored.
- Batch size must be 100,000 rows.
You need to add a destination and configure it to meet the requirements. Which destination should you use?
Options
- AOLE DB Destination with Fast Load
- BSQL Server Destination
- CADO NET Destination without Bulk Insert
- DADO NET Destination with Bulk Insert
- EOLE DB Destination without Fast Load
How the community answered
(18 responses)- A78% (14)
- C6% (1)
- D6% (1)
- E11% (2)
Why each option
OLE DB Destination with Fast Load is the only destination that supports KEEPIDENTITY, configurable batch sizes, and remote server connections simultaneously.
OLE DB Destination with Fast Load uses bulk insert operations and exposes advanced options including Keep Identity (to preserve source identity values instead of generating new ones), Keep Nulls, Table Lock, and Rows Per Batch (supporting 100,000-row batches). Unlike SQL Server Destination, it connects via OLE DB and works across servers, allowing the package on Server B to load into Server A.
SQL Server Destination uses the internal bulk-copy API which requires a local in-process connection to the destination SQL Server, making it unusable when the package runs on Server B and the table is on Server A.
ADO NET Destination without Bulk Insert performs row-by-row inserts and does not support batch sizes or KEEPIDENTITY functionality.
ADO NET Destination with Bulk Insert does not expose the KEEPIDENTITY or KEEPDEFAULTS options available in OLE DB Fast Load.
OLE DB Destination without Fast Load performs row-by-row inserts and does not support bulk batch sizes or the KEEPIDENTITY option.
Concept tested: SSIS OLE DB Destination Fast Load with identity and batch configuration
Source: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination
Topics
Community Discussion
No community discussion yet for this question.