Difference between .NET, OLEDB, and Native Providers in SSIS

Question

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

• Microsoft OLE DB Provider for SQL Server
• SQL Server Native Client 10.0

Native OLE DB

• Microsoft OLE DB Provider for SQL Server
• SQL Server Native Client 10.0

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.

Huberman

Answer 1

You really don't have to worry too much about which one to use, select the Native one (SQLNCLI) and it'll work fine out of the box.

Burge

from SSIS when i try to connect to my SQL Server 2014 it works fine with Microsoft OLE DB Provider for SQL Server but it gives errors while i use OLEDBSource task in DataFlow. Also if i try to use SQL Server Native Client 11.0 with same server it gives error in test connection itself that "Error in initializing the provider". any idea about what could be the problem with my setup? – Rubescent

Your answer is saying all are same". If it is not then you should mention why something is better than the other or something is better in some scenarios over the other etc., – Menado

Answer 2

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.

Picrate

Answer 3

Latest from Microsoft here: https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver15

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.

The SQL Server Native Client (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) aren't recommended for new application development.

For new projects, use one of the following drivers:

Microsoft ODBC Driver for SQL Server https://learn.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

Microsoft OLE DB Driver for SQL Server https://learn.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-server?view=sql-server-ver15

I am using SQL Server 2022 patched running SSIS through VS 2022. SSIS on SQL Server 2022 will throw an error if you try to use the native driver.

Flatto