Installing SQL Server Data Tools

SSDT is a specialized edition of the Microsoft Visual Studio integrated development environment (IDE). It can be used to develop a variety of projects related to SQL Server, such as relational and Azure databases, Analysis Services data models, Reporting Services reports, or Integration Services projects, to name just a few.

In the past, SSDT used to be a standalone tool, requiring only a subset of Visual Studio features to be installed. However, starting with Visual Studio 2019 (or SQL Server 2019), SSDT represents an extension (or better: a set of extensions) of the principal Visual Studio IDE.

To use the 2019 version of SSDT, you first need to install Visual Studio 2019, and then add the SSDT extensions, followed by the extensions needed to develop Analysis Services, Integration Services, and/or Reporting Services projects. This does increase the complexity of the installation procedure, but it also more closely follows the standard approach of adding features to the core Visual Studio installation.

Getting ready

If you do not have a valid Visual Studio 2019 Professional or Enterprise license at your disposal, you can also use the free Community edition of Visual Studio 2019. All editions are available to download at https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt. This location can also be accessed from SQL Server Installation Center.

How to do it…

Use your favorite internet browser to navigate to the Visual Studio 2019 download site listed previously, or start SQL Server Installation Center, and then on the Installation page, select the Install SQL Server Data Tools option.

This recipe assumes that Visual Studio 2019 has not yet been installed on the workstation, and the instructions will show you how to install Visual Studio 2019 Community Edition:

1. On the Download SQL Server Data Tools (SSDT) for Visual Studio web page, in the Install SSDT with Visual Studio 2019 section, locate the link to Visual Studio 2019 Community, and follow that link to the Visual Studio 2019 Downloads page. Click FREE DOWNLOAD in the Community column to start the download operation and save the file to the local drive on your workstation.
2. When prompted, click Run to start the installation; otherwise, use Windows Explorer to navigate to your folder, and start the installation by opening the vs_Community.exe file.
3. The installation begins with the setup of a few prerequisites. You can take this moment to inspect the Microsoft Privacy Statement and the Microsoft Software License Terms by following the links provided in the message box:
   

   Figure 1.21 – Visual Studio Installer

   When ready, click Continue.

   The installation now downloads and extracts the files, after which the Installing — Visual Studio 2019 Community dialog should open.

4. On the Workloads page, in the Desktop & Mobile section, check .NET desktop development, and in the Other Toolsets section, check Data storage and processing:
   

   Figure 1.22 – The Visual Studio 2019 installation details

   Check the list of items in the right column – the same items should be listed as the ones shown in the screenshot.

5. Leave the Install while downloading option selected in the combo box next to the Install button.

   When ready, click Install to continue.

   The installation should take a few minutes, depending on your internet connection, after which the Visual Studio will greet you with the Sign in screen.

6. Click Not now, maybe later to skip the sign-in process for the time being.
7. In the next dialog, select your development settings and your color theme, and click Start Visual Studio.

   Of course, you can also change these settings in SSDT later.

8. After a few minutes, Visual Studio 2019 should open, with the Open recent/Get Started dialog.

   At this time, you only need to access the Visual Studio 2019 IDE to install additional SQL Server extensions.

   Click Continue without code.

9. Visual Studio opens without any solutions or projects loaded.

   In the Extensions menu, select the Manage Extensions command.

10. In the Manage Extensions dialog, on the Online | Visual Studio Marketplace page, use the search text box located at the top right to search for integration services, as shown in the following screenshot:
    

    Figure 1.23 – The Visual Studio 2019 extensions

    In the search result list, locate SQL Server Integration Services Projects and click Download.

11. An internet browser window should open, prompting you to save the file to your local drive. Save the file to the local drive on your workstation.
12. If prompted by the browser to run the file, click Run to start the installation; otherwise, use Windows Explorer to navigate to your downloads folder, and open the Microsoft.DataTools.IntegrationServices.exe file to start the installation.
13. In the first dialog, select the language to be used during the installation. You can use any of the supported languages; however, note that these instructions assume that you selected the English language. When ready, click OK to continue.
14. In the next dialog, simply click Next to continue.
15. In the next dialog, make sure that your edition of Visual Studio 2019 is selected as the installation target, but do not click Install yet.
16. Switch to Visual Studio 2019, close the Manage Extensions dialog, and then close Visual Studio as well.

    Important note

    During the installation of a Visual Studio extension, changes might have to be made to Visual Studio files, which cannot be done if these files are open. Therefore, this is the perfect moment during the installation to close Visual Studio. If files are open, an error message might be displayed, prompting you to close them before being able to continue.

17. Switch back to the Visual Studio extensions installer, and now do click Install to continue.
18. If prompted by the operating system whether to allow SQL Server Integration Services Projects to make changes to your device, click Yes to continue. The installation should complete within a few minutes.
19. In the final dialog, click Close to finish the installation.

Close the internet browser window that was used to download the SQL Server Integration Services Projects installer, and Visual Studio Installer, if they are still open.

How it works…

You have installed Visual Studio 2019 with the features needed to complete the rest of the recipes in this book. These recipes will cover typical SSIS development, but also control flow and data flow customization.

You can use the same setup to complete many other development tasks, and if needed, you can install any additional features at any time, by running Visual Studio Installer – available from the Windows Start menu.

Important note

In order to continue using the Community edition of Visual Studio 2019 beyond the 30-day evaluation period, you will have to sign in with a Microsoft Live account. You can use the same account that you will be using later in this chapter to create the Azure subscription.

A few days before the evaluation period expires, if you try to open SSDT, you will be prompted to sign in, but you can also sign in at any time.