Menu
I am pretty confused with the plethora of OLEDB providers found in creating a connection to a database in SSIS 2008 R2.
I would much appreciate if you could tell me what the following providers stand for and when is best to use them:
.Net Providers for OleDB
Native OLE DB
I am pretty unsure which provider to use out of these if I want to create an OLE DB connection to the database in question. Additionally, I am confused why the same type of provider appears both in .Net and Native.
778
OLEDB is much faster than the SQLClient, EXCEPT when it is access through ADO.NET. The drivers for OLEDB are written in native unmanaged code however, when you access these drivers through ADO.NET, you have to go through several layers (including an abstraction layer and a COM interop layer).
ODBC is constrained to relational data stores; OLE DB supports all forms of data stores (relational, hierarchical, etc) In general, OLE DB provides a richer and more flexible interface for data access because it is not tightly bound to a command syntax (like SQL in the case of ODBC).
The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.
"The advantage of using OleDbConnection is flexibility. You can change your database (for instance, move to Oracle)and not have to change your code. The advantage of SqlConnection is performance. The SqlConnection is tuned specifically for accessing Sql Server.
Here's some more detailed information about the main two connections (OLE DB and ADO.NET). It seems to me that ADO.NET is better in the sense that it should work more universally in different environments.
115
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
