You are developing a SQL Server Integration Services (SSIS) package to load data into a
SQL Server table on ServerA, 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?
A.
OLE DB Destination with Fast Load
B.
SQL Server Destination
C.
ADO NET Destination without Bulk Insert
D.
ADO NET Destination with Bulk Insert
E.
OLE DB Destination without Fast Load
Explanation:
http://msdn.microsoft.com/en-us/library/ms141237.aspx
http://msdn.microsoft.com/en-us/library/ms139821.aspx
http://msdn.microsoft.com/en-us/library/ms141095.aspx
I’m surprised there isn’t more discussion on this. Is the answer so obvious?
https://datatellblog.wordpress.com/2015/01/13/ssis-data-flows-ado-net-vs-ole-db-vs-odbc/
ADO is slower than ole db. Sql Server is only good locally. Both ADO and ole db allows for 100,000 row batches
oledb allows destinations to have their own identities and ignore constraints
https://www.mssqltips.com/sqlservertip/1840/sql-server-integration-services-ssis-best-practices/
As far as I can tell ADO doesn’t explicitly have these options, but so what? Does it always choke on the constraints? A is probably best
1
0
Henry,
ADO and OLE both have BatchSize Property, but if you want to keep identity from the source table (and prevent generating new ids in the destination table) the most simple option is OLE DB Destination (you’ll have only check the radio box called “Keep Identity”).
In ADO NET we can keep identity too, but its far more complicated and you”ll have to create 2 EXECUTE SQL TASK, in the first you execute SET IDENTITY_INSERT ON and after the DATAFLOW you SETN IDENTITY_INSERT OFF, Then you should disable Bulk Insert option, which will lead to a bad performance (Very bad performance)
0
0