SSIS Download, Install and Configure

Problem

SQL Server Integration Services (SSIS) is an ETL tool that comes with SQL Server. SSIS allows you to write simple or complex processes to import, export and manipulate data. This can also be used for SQL Server automation tasks. In this tutorial, we walk through an SSIS download and the steps to install SSIS whether it be for a new installation of SQL Server or just adding SSIS to an existing SQL Server install.

Solution

This article will explain how to download and install SQL Server Integration Services (SSIS).

What is SSIS?

DBAs use SSIS to automate administrative tasks in SQL Server, while Developers\Business Intelligence professionals use it for data integration (ETL and ELT). For example, you can perform the following tasks in SSIS:

Download and Install SSIS

First, go to the download area on the Microsoft SQL Server website.

You can download SQL Server on-premises or the developer edition if you use it for development purposes.

The SQL Server installer includes the SSIS component.

Once downloaded, run the installer.

Choose from three options.

The basic option installs the SQL Server.
For this example, select the Custom option so we can install SSIS.
The third option, Download Media, downloads the installer to install later.

Select the location to install SQL Server.

Select the New SQL Server standalone installation or add features to an existing installation option.

Microsoft Update

The next screen is to apply the latest Microsoft security updates.

Install Rules

Next, the Install Rules provide a checklist of requirements that must be validated before continuing, like activating the firewall or checking that our machine is not a domain controller. The installer also checks for consistency in registry keys and that the correct dotnet framework is installed.

Installation Type

Then, additional options appear like installing a new SQL Server or adding new features. For this example, we will add features, specifically SSIS.

Azure Exertion for SQL Server

There is also an option to install extensions to handle Azure. This tutorial is dedicated to installing SSIS on-premises, so we will not install the Azure extensions. This feature is useful if we want to work with Microsoft services in the cloud (Azure).

Feature Selection

Under Feature Selection, select the Integration Services option. The options for Integration Services are related to Scale Out, which provides high performance for the package execution by distributing the package using multiple servers. That way, you can run the packages in parallel across multiple servers. To learn more, check out the SSIS Scale Out Cluster tutorial.

The SSIS selection provides you with two options:

Scale Out Master: Is used to Scale Out and manage the Scale Out system using the SSISDB Catalog and the Scale Out Master service. This Catalog stores all the package information and the execution information.
Scale Out Workers: This pulls the execution tasks from the Scale Out Master. They also run the packages.

Server Configuration

For Server Configuration, select the account name to handle the Integration Services service. Here you can also select the Startup Type for the service.

Ready to Install

Now, click Install to start the installation.

Complete

If everything ran successfully, a Complete message will appear.

That’s it, for the SSIS download and install and it’s now ready to be used.

Installing Visual Studio (optional steps)

Once SSIS is installed, you may want to create packages to test the software. To create packages, you need Visual Studio. You can download Visual Studio 2022 here. For more details about installing Visual Studio, here is a related tutorial.

Adding SSIS Extensions

With Visual Studio installed, go to Extensions in the menu.

Select the option Manage Extensions.

Search for the SQL Server Integration Services Projects 2022 and install it.

Create and Test Your First SSIS Project

After installing the SSIS extension, let’s create a package: copy a backup (Adventureworks2019.bak) from a local folder (c:\data) to a shared network folder.

First, go to File > New in Visual Studio.

Type a name and provide a location for the project. Click Create.

In the SSIS project, drag and drop the File System task to the control pane.

Double-click the File System Task.

Click in the Source Connection.

In Usage type, select Existing file. This option allows us to copy an existing file, which, in this example, is an existing backup.

Type the path of the SQL backup to copy.

Next, click on Destination Connection.

Select the Existing folder and type the path for the existing destination folder.

Execute the task.

If everything goes as planned, the backup has been copied to the shared destination folder.

Next Steps

For more information about SSIS, check out these links:

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018