Today I want to go through the steps to install the SQL Server Data Tools (SSDT) and SQL Server Integrations Services Projects.

Download SQL Server Data Tools (SSDT) for Visual Studio
https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt

Changes in SSDT for Visual Studio 2019
The core SSDT functionality to create database projects has remained integral to Visual Studio.

With Visual Studio 2019, the required functionality to enable Analysis Services, Integration Services, and Reporting Services projects has moved into the respective Visual Studio (VSIX) extensions only.

There’s no SSDT standalone installer for Visual Studio 2019 as in contrast to the predecessor Visual Studio 2017..


If Visual Studio 2019 is already installed, you can edit the list of workloads to include SSDT. If you don’t have Visual Studio 2019 installed, then you can download and install Visual Studio 2019 Community.

To modify the installed Visual Studio workloads to include SSDT, use the Visual Studio Installer.




Launch the Visual Studio Installer. In the Windows Start menu, you can search for installer.


  1. In the installer, select for the edition of Visual Studio that you want to add SSDT to, and then choose Modify.
  2. Select SQL Server Data Tools under Data storage and processing in the list of workloads.

For Analysis Services (SSAS), Integration Services (SSIS), or Reporting Services projects (SSRS), you can install the appropriate extensions from within Visual Studio with Extensions > Manage Extensions or from the Marketplace.


Installation of the SQL Server Integrations Services Projects

Download starts from https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects

Run the Microsoft.DataTools.IntegrationServices.exe

Now we can open Visual Studio 2019 – Create a new project – Integration Services Project



AdventureWorks sample databases


https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure

This article provides direct links to download AdventureWorks sample databases, as well as instructions for restoring them to SQL Server and Azure SQL Database.

For more information about samples, see the Samples GitHub repository.


Download backup files

https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=ssms#download-backup-files

Use these links to download the appropriate sample database for your scenario.

  • OLTP data is for most typical online transaction processing workloads.
  • Data Warehouse (DW) data is for data warehousing workloads.
  • Lightweight (LT) data is a lightweight and pared down version of the OLTP sample.



https://improveandrepeat.com/2019/02/use-the-adventureworks-sample-database-for-your-examples/

Microsoft created a long time ago the fictitious multinational manufacturing company called Adventure Works and shipped the AdventureWorks database as part of SQL Server. That stopped a few releases ago, but the database still does exist. With nearly 20.000 customers, over 70.000 orders and 500 products there is enough data for your examples. Thanks to the liberal license (MIT license), you are allowed to use this database for whatever you like.

You can choose between these editions:

  • OLTP: all you need (48 MB)
  • Lightweight (LT): with a smaller set of data (7 MB)
  • Extended data warehouse (DW_Ext): with a lot more data (883 MB)

The file size is small, but there are many tables inside this database.

AdventureWorks2008_db_diagramDownload



AdventureWorks sample databases
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks


AdventureWorks sample Database for Analysis Service
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks-analysis-services




Workloads in Visual Studio

https://tutorials.visualstudio.com/vs-get-started/customizing

When you installed Visual Studio, you probably had the option to install different workloads. A workload is a bundle of tools within Visual Studio that helps you be productive with certain languages or platforms. For example, the Desktop development with C++ workload includes Visual Studio features that let you run and debug C++ console applications and see suggestions for your C++ code.

You can add or remove workloads after your initial installation of Visual Studio to add support for additional languages and tools. To do so, run the Visual Studio Installer again using the Windows Start menu, then click Modify.



Troubleshooting

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.



More about SSIS you will find also in the the following posts.

SQL Server Integration Services (SSIS) Basics – Load Data from a MySQL Database into SQL Server
SQL Server Integration Services (SSIS) Basics – Script Task