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:
- Import Excel unicode data with SSIS
- Export SQL Server Data to Oracle using SSIS
- Export MySQL data to Excel using SSIS
- SSIS Package to Automate Code Deployment of Database Changes
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:
