SSIS projects connection manager driver

Question

SSIS projects connection manager driver

sqlfriend

SSC Guru

Points: 52781
More actions
October 27, 2020 at 5:45 pm
Go to Answer
#3801794

Hi I am starting using the project deployment mode. I am using SQL server 2019
And I see when I add a connection in connection manager for a package, I cannot see the sql server native client driver any more.
What connection manager type do you recommend for SQL server connections?

Thanks
Phil Parkin

SSC Guru

Points: 246960
More actions
October 28, 2020 at 5:16 pm
Answer
#3802176

MSOLEDB is available:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

The SQL Server Native Client (SQLNCLI) remains deprecated and it is not recommended to use it for new development work. Instead, use the new Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) which will be updated with the most recent server features.

https://docs.microsoft.com/en-us/SQL/relational-databases/native-client/SQL-server-native-client?view=sql-server-ver15

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thank you, but when I tried to add a connection manager, in the provider list, I don't see there is a MSOLEDSQL. I do see one named microsoft OLEDB provider for SQl server, but when I add it and look at the connection string, it is something like

Data Source=myserver;Initial Catalog=Truancy;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=SSIS-2019SSIS-{9CDABED8-B895-4E1F-86F9-1BE15149E31F}mserver.mydb;Auto Translate=False;

It is SQLOLEDB not MSOLEDB.

Also in the built in connection manager list on this page, I cannot find one with MSOLEDBSQL,

https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/integration-services-ssis-connections?view=sql-server-ver15

Thom A SSC Guru Points: 99130 More actions · Answer 3

"Microsoft OLEDB provider for SQL server" is the one you want. If you've selected that one, try it, it'll work.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sqlfriend SSC Guru Points: 52781 More actions · Answer 4

Thanks. Yes it works. But if I look at the connection string: it is like I posted earlier:

Data Source=myserver;Initial Catalog=Truancy;Provider=SQLOLEDB.1;Integrated Security=SSPI;Application Name=SSIS-2019SSIS-{9CDABED8-B895-4E1F-86F9-1BE15149E31F}mserver.mydb;Auto Translate=False;

It is SQLOLEDB not MSOLEDB.

And microsoft online doc said it is deprecated.

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

Thanks, I tried again, this time I did get MSOLEDBSQL.1; in the connection string.

I wonder how I got SQLOLEDB.1 in early operation, I remember I chose the same one.

Thanks all