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.
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:
- SQL Server Analysis Services (SSAS)
- SQL Server Reporting Services (SSRS)
- 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.
The installation process begins. It takes some time to complete.
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)
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.
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.
The visual studio will be started. To create a SQL Server integration service package, click on Create new project link.
On the New Project dialog box, expand Business Intelligence and select Integration Services. Select Integration Services Project. Specify desired SSIS project name.
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
A dialog box named Shrink Database Task opens. First, we must configure the SQL Server connection. To do that, click on New.
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:
Now, select the desired database from the Databases drop-down box. We want to shrink the AdventureWorks2017 database, so select it.
Back to the Shrink Database Task dialog box, specify the following parameters.
- Specify the size of the database that will trigger the execution of the Shrink Database Task. I am going to use the default values
- 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.
The control flow window looks like the following image:
Test the execution of the package
To execute the SSIS package, click on the Start option located in the Menu bar.
Once the package completes successfully, it looks like the following:
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.
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.
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.
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.
On the Review your select screen, you can verify the source and destination for package deployment. Click on Deploy.
The deployment process begins. Once it completes, the screen looks like the following. Click on Close to end the wizard.
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.
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.
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
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022