VS 2019 SSIS package can't add SQL Server

8
votes

I installed Visual Studio 2019 and the SSIS package extensions and created a new project. I'm able to add my SQL Server as a Connection Manager with no issues, but when I then drag a Data Flow Task to the package and drag a Source Assistant into that, I'm not able to select my SQL Server.

The source types shown are Excel, Flat File, and Oracle. If I uncheck the "show only installed source types" then I see SQL Server but I can't pick it.

The interesting thing is on this same box I've been editing an SSIS project with Visual Studio 2017 so I know the SQL Server stuff works fine. However, if I open that existing project none of the connection managers show up in Visual Studio 2019.

1
sql-serverssisvisual-studio-2019ssis-2019
What type of connection manager is your SQL Server connection? – Nick.McDermaid
An OLEDB connection. The provider shows as SQLNCL11.1 – Gargoyle
OK that's weird. I assume it's an installation issue. What happens if you drag an OLE DB Source on instead of using the Source Assistant? – Nick.McDermaid
I don't have an OLE DB source as an option. – Gargoyle
If you right click in Connection Manager do you have New OLE DB Connection... as an option there? – Jacob H

1 Answers

9
votes

Answer: You need the new Microsoft® OLE DB Driver 18 for SQL Server

Context:

This was a s*** show right out of the box (see the comments on GitHub).

https://github.com/MicrosoftDocs/visualstudio-docs/issues/2592

Apparently SSIS was not included in the installer and so it does not install even when you select the Data Storage and Processing package in the installer. SSIS currently can only be installed by downloading via the Marketplace here:

SQL Server Integration Services Projects - Visual Studio Market Place

Under the known issues section, item 1:

SQL Server Native Client (SQLNCLI11.1) is deprecated and not installed by VS2019. We recommend upgrading to the new Microsoft OLE DB driver for SQL Server. If you want to continue using SQL Server Native Client, you can download and install it from here.