Installing SQL Server Integration Services


By:   |   Updated: 2020-11-12   |   Comments   |   Related: More > Integration Services Administration


Problem

SQL Server Integration Services (SSIS) is one of the major components of the Microsoft Business Intelligence (MSBI) stack. It's the Extract, Transform and Load (ETL) tool that consolidates data from different sources, transforms it, and loads it to a destination. SSIS replaced Data Transformation Services (DTS) in SQL Server 2005. It's included with the Standard, Business Intelligence and Enterprise editions of SQL Server.

You're tasked with installing SSIS as part of a SQL Server installation or adding it to an existing one and would like a step by step guide showing how to install it.

Solution

We'll walk through each of the steps of installing SSIS 2019. For our example we have SQL Server 2019 Standard Edition already installed on Windows Server 2019. 

Minimum Hardware and Software Requirements

The minimum hardware and software required to install SQL Server are:

  • Windows Server 2016 or Windows 10 TH1 1507
  • 6 GB of available hard disk space
  • 4 GB memory
  • 1.4 GHz minimum 64-bit CPU

Install SSIS

SSIS can be installed along with or added to an existing install of SQL Server.

  1. To start the install, double click setup.exe on your installation media and the first screen will open
SSIS Install Screen 1

The SQL Server Installation Center screen has links to a number of related tools and is where we start the install.

  1. Choose 'Installation' from the list on the left side to go to the next screen
SSIS Install Screen 2

This step is the same whether we're doing a new SQL Server installation or adding to an existing one.

  1. Choose 'New SQL Server stand-alone installation or add features to an existing installation' from the list on the right side
SSIS Install Screen 3
  1. Check off 'I accept the license terms andů'
  2. Next
SSIS Install Screen 4

Note: You probably would have a more controlled method in place for SQL Server updates and not want them automatically done but you have the option.

  1. Check 'Use Microsoft Update to check for updates' if you want to automatically check, otherwise leave unchecked
  2. Next
SSIS Install Screen 5
  1. Click on Warning to get a link to what ports need to be open if the Windows Firewall service is running on the machine that you're installing the SQL Server on
  2. Next
SSIS Install Screen 6

Note: If we were installing a new SQL Server, we would leave the 'Perform a new installation of SQL Server 2019' radio button selected.

  1. Select the 'Add features to an existing instance of SQL Server 2019' radio button and choose the instance we're adding to in the dropdown
  2. Next
SSIS Install Screen 7

Note: If we were installing a new SQL Server, we would check the 'Database Engine Services' box it's already installed as indicated by the grayed-out checkbox.

  1. Check Integration Services
  2. Next
SSIS Install Screen 8

Note: Best practice is to not use the default account to run any of the SQL Server services. Microsoft Docs has more information here: Configure Windows Service Accounts and Permissions

  1. Choose service account to run the SSIS service
  2. Verify 'Startup Type' is Automatic
  3. Next
SSIS Install Screen 9

We have the opportunity to review a summary of what we're installing here.

  1. Verify Summary
  2. Install
SSIS Install Screen 10

At this point the installation is done and we now have SSIS installed.

  1. Verify install was successful
  2. Review log file
SSIS Install Screen 11
  1. Close setup screen
SSIS Install Screen 12

Create Integration Services Catalog

At this point we have the base install of the SSIS engine done. Now we need to manually create the Integration Services Catalog. The Catalog is where SSIS projects are deployed to and managed. The Integration Services Catalog is stored in the SSIS database that is created as part of this process.

Open SQL Server Management Studio (SSMS) and connect to the SQL Server we just installed SSIS on.

  1. Expand server dropdown
  2. Right click on 'Integration Services Catalog'
  3. 'Create Catalog…' to open the Catalog Creation Wizard
Create SSIS Catalog Screen 1
  1. Check 'Enable CLR Integration' checkbox to enable Common Language Runtime (CLR) so SSIS can run CLR stored procedures
  2. Check 'Enable automatic execution of Integration Services stored procedure at SQL Server startup'  so the state of operations for the SSISDB catalog is performed when the service is started.
  3. Enter strong password (and be sure save in your password manager)
  4. Retype password
  5. OK to create Catalog
Create SSIS Catalog Screen 2

Now, if we expand the Database and Integration Services dropdowns in SSMS, we see the SSISDB database (note: name cannot be changed) and Integration Services Catalog have been created.

Create SSIS Catalog Screen 3

This next step is optional but depending on how active your SSIS server will be you may want to change the SSIS history retention to manage the size of the SSISDB database as it can grow rather large.

First, run the following to verify cleanup is enabled and to obtain the number of days of retention:

-- is cleanup enabled and number of retention days 
SELECT [property_name], [property_value]
FROM [SSISDB].[internal].[catalog_properties]
WHERE property_name IN ('OPERATION_CLEANUP_ENABLED', 'RETENTION_WINDOW');  

We see that OPERATION_CLEANUP_ENABLED = TRUE because we checked 'Enable automatic execution of Integration Services stored procedure at SQL Server startup' during the engine install and RETENTION_WINDOW = default = 365 days.

Configure SSIS Catalog Screen 1

The default RETENTION_WINDOW is probably OK if the SSIS server won't be that active. However, if it will be active and you're concerned with the SSISDB database getting too large and there is no requirement to keep 365 days of history it's very easy to change with the It's done with the [catalog].[configure_catalog] stored procedure in SSISDB by telling it what to change and what to change it to.

In this example we'll change the RETENTION_WINDOW to 90 days.

-- change number of retention days 
EXEC [SSISDB].[catalog].[configure_catalog] RETENTION_WINDOW, 90; 

Run the following to verify the change:

-- verify change
SELECT [property_name], [property_value]
FROM [SSISDB].[internal].[catalog_properties]
WHERE property_name IN ('RETENTION_WINDOW');

And we see it's been changed to 90.

Configure SSIS Catalog Screen 2

SSIS Cumulative Updates (CU)

Cumulative Updates, if applicable, are installed as part of the SQL Server CU. Click here for more information about SQL Server versions.

Next Steps

Now that we have SSIS installed and catalog created here are some links to get you started with creating and executing SSIS Packages:



Last Updated: 2020-11-12


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips





Comments For This Article





download





Recommended Reading

Reporting with the SQL Server Integration Services Catalog

Copy or Duplicate SQL Server Integration Services (SSIS) Environments using T-SQL

Find Detailed Error Messages for SQL Server Agent Job Step History for an SSIS Package

Find SSIS Package Stuck in Running Status

SSIS Catalog Deployment to Support Dev, QA and Production














get free sql tips
agree to terms