Nisarg Upadhyay
Execute the package

Install and configure SSDT 2017 on Windows 10

October 28, 2021 by

In this article, we are going to learn how we can install and configure the SSDT 2017 on Windows 10. The SQL Server data tools 2017 (SSDT 2017) is a development tool used for database development, SQL Server analysis service data models, SQL Server report service reports, and Integration service packages.

Installation Process

We are using the stand-alone installer of SSDT 2017, which can be downloaded from here. It downloads a file named SSDT-Setup-ENU.exe. Once it is downloaded, double-click on it to begin the installation.

Welcome Screen

The first screen does not have any information about the SQL Server data tools. You can see the release number on top of the screen. There are a few links are provided that navigates to the License terms and Privacy statement. You can review them and click on Next.

Welcome screen

Select Features to install

On the next screen, you can view the list of tools that you want to install. These tools are used to develop new integration service packages, analysis cubes, and SSRS reports. Using SSDT installer, we can install tools for the following features:

  1. SQL Server Analysis Services (SSAS)
  2. SQL Server Reporting Services (SSRS)
  3. SQL Server Integration Services (SSIS)

Also, you can set the nickname of the visual studio 2017 instance.

We are installing development tools for all features, so I have selected all features. Click on Install.

Specify the tools for features

The installation process begins. It takes some time to complete.

SSDT 2017 installation in process.

The installation of SQL Server data tools has been installed.

Create an SSIS package

Let us create a SQL Server integration service package. For demonstration, I am creating a simple package that will shrink the database. First, launch Visual Studio 2017. (SSDT 2017) Open Start Menu 🡪 Select Visual Studio 2017. (SSDT 2017)

Open SSDT 2017

If you have a visual studio subscription, you can sign in to the Visual Studio account. You can create a new visual studio account by clicking on Create one link. We do not have the visual studio subscriptions, so click on Not now, maybe later link.

Login to visual studio

On the next screen, you can select Development settings and choose the color theme. I am using default settings and click on Start Visual Studio.

Choose the color theme for visual studio

The visual studio will be started. To create a SQL Server integration service package, click on Create new project link.

Create new project

On the New Project dialog box, expand Business Intelligence and select Integration Services. Select Integration Services Project. Specify desired SSIS project name.

Create New SSIS Project

We are going to create an SSIS package that is used to shrink the user database. For that, we are using the Shrink database task. In the SSIS Toolbox, locate the Shrink database task, drag, and drop it on the Control flow section of the package designer. Double-click on Shrink Database Task to configure it

Drag and drop Shrink Database Task

A dialog box named Shrink Database Task opens. First, we must configure the SQL Server connection. To do that, click on New.

New SQL Server Connection

Specify the connection parameters as follows:

  • Connection Name: Provide desired connection name
  • Server Name: Specify the hostname of the server on which you want to shrink the database. In our case, it is Nisarg-PC
  • Authentication Method: Specify the type of authentication you want to use to connect to the SQL Server. We can use SQL Server authentication as well as Windows authentication. I have selected Windows authentication

Screenshot of connection properties dialog box:

Specify the connection parameters

Now, select the desired database from the Databases drop-down box. We want to shrink the AdventureWorks2017 database, so select it.

Select database

Back to the Shrink Database Task dialog box, specify the following parameters.

  1. Specify the size of the database that will trigger the execution of the Shrink Database Task. I am going to use the default values
  2. Specify the amount of free space that you want to keep after the shrink operation completes. Once the task reaches the specified values, it will stop the execution. I am going to use the default values

You can set an option to keep the free space in database files or return the free space to the operating system. We want to keep the free space in database files, so I have chosen the “Retain freed space in database files” option.

Configure the parameters

The control flow window looks like the following image:

SSIS package designer surface

Test the execution of the package

To execute the SSIS package, click on the Start option located in the Menu bar.

Execute the package

Once the package completes successfully, it looks like the following:

Package executed successfully

Now, let us understand the process of deploying the package.

Deploying SSIS packages

Now, let us deploy this package. To do that, open solution explorer, right-click on Integration Service Project, and select Deploy.

Deploy SSIS package using SSDT 2017

The Integration Service Project Deployment Wizard starts. On the Welcome screen, you can see the details of the wizard and the tasks that are going to be performed by it. Click on Next.

Deployment Wizard Welcome screen

We are running this package on SQL Server, deploying the SSIS project on the SSISDB hosted on SQL Server. Select the “SSIS in SQL Server” option. Click Next.

select deployment target

On the Select Destination screen, specify the server name on which the integration service has been installed. Specify the authentication method and provide the path of the folder that is created in the SSIS catalog. Click Next.

Select SSISDB as destination

On the Review your select screen, you can verify the source and destination for package deployment. Click on Deploy.

Review the selection

The deployment process begins. Once it completes, the screen looks like the following. Click on Close to end the wizard.

Package deployed successfully

You can view the deployed package under the Integration services catalogs node of the SSISDB. Open SSMS 🡪 Connect to the SQL Server database engine 🡪 Expand Integration Services catalogs 🡪 Expand SSISDB 🡪 SSIS Maintenance plan 🡪 Expand Projects.

View integration service in SSISDB catalog

As you can see, the package has been deployed successfully.

Summary

In this article, I have explained how to install SQL Server data tools 2017. Additionally, we created a simple SSIS package that shrinks the adventureworks2017 database. We also covered the step-by-step deployment process of the SQL Server integration package.

Nisarg Upadhyay
Data Tools (SSDT), SQL Server 2017

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

402 Views