Post reply

SSIS projects connection manager driver

  • 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

  • October 27, 2020 at 6:47 pm

    #3801813

    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

     

     

  • October 27, 2020 at 8:38 pm

    #3801860

    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

     

  • October 28, 2020 at 9:09 am

    #3802011

    "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

  • October 28, 2020 at 4:55 pm

    #3802166

    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.

  • October 28, 2020 at 5:16 pm

    Answer

    #3802176

    MSOLEDB is available:

    ConnMgr

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 28, 2020 at 5:34 pm

    #3802192

    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

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

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