CODEWITHSUNDEEP
sql-serversql-server-2008ssis
picmate 涅
picmate 涅

Reputation: 4249

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

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.

Upvotes: 13

Views: 14371

Answers (3)

Ian Jowett
Ian Jowett

Reputation: 324

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.

Upvotes: 0

carlowahlstedt
carlowahlstedt

Reputation: 158

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.

Upvotes: 3

cairnz
cairnz

Reputation: 3957

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.

Upvotes: 0

Related Questions