VS 2019 SSIS package can't add SQL Server

Question

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.

Corydalis

What type of connection manager is your SQL Server connection? – Snail

An OLEDB connection. The provider shows as SQLNCL11.1 – Corydalis

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? – Snail

I don't have an OLE DB source as an option. – Corydalis

If you right click in Connection Manager do you have New OLE DB Connection... as an option there? – Lankton

Yup, and that's where my data connection that I want to use is defined. – Corydalis

Looks like I have no now add it as an ADO.NET data source, and then use an explicit "ADO NET Source" instead of the source assistant from favorites. – Corydalis

In your SSIS Toolbox in the data flow surface, under Other Sources you don't have a OLE DB Source listed? You have other problems then. – Snail

The same thing happened to me. In the wizard window there is a check box "Show only installed destination types", if you remove the check, you will see the SQL Server option, and when you select it, it will indicate that you need to install the OLE DB connectivity driver for SQL Server. I checked that this driver solves it, as indicated in the answer below. – Inmost

Answer 1

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.

Newsmagazine

What connection string should look like with this new driver? 😐 – Thun

@Thun I do not know. Best to create a new connection through the UI and then retrieve the ConnectionString from the properties field. – Newsmagazine

So Visual Studio 2019 doesn't come with SSIS and and SSIS for VS 2019 doesn't come with SQL Server support 😒 – Salinas

@Salinas No, SSIS is currently available for VS2019; however, it is not yet available for VS2022. This probably has to do with the fact that VS2022 is now 64-bit and SSIS is still a 32-bit package. I believe Microsoft is working on a 64-bit version of SSIS for VS2022. ANYWAYS, SSIS has always supported SQL Server and will continue to do so. This Q&A concerned the fact that Microsoft brought out a new driver for SQL Server and deprecated an existing one. This driver is available in SSIS - developers just need to update all of their packages that use the deprecated driver. – Newsmagazine

In the wizard window there is a check box "Show only installed destination types", if you remove the check, the SQL Server option will appear, and when you select it, it will indicate that you need to install that driver with its respective version. I checked that, indeed, it solves the problem. – Inmost