Install SSIS Step by Step (2024)

By: Joe Gavin |Updated: 2023-06-28 |Comments (5) | Related: > Integration Services Administration

Problem

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

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

Solution

We'll walk through each of the steps of installing SSIS 2019. For our examplewe have SQL Server 2019 Standard Edition already installed on Windows Server 2019.These steps were also tested with SQL Server 2022 and everything works prettymuch the same way.

Minimum Hardware and Software Requirements

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

• Windows Server 2016 or Windows10 TH1 1507
• 6 GB of available hard diskspace
• 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 mediaand the first screen will open

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

1. Choose 'Installation' from the list on the left side to go tothe next screen

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

1. Choose 'New SQL Server stand-alone installation or add features toan existing installation' from the list on the right side

1. Check off 'I accept the license terms and…'
2. Next

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

1. Check 'Use Microsoft Update to check for updates' if you wantto automatically check, otherwise leave unchecked
2. Next

1. Click on Warning to get a link to what ports need to be open if the WindowsFirewall service is running on the machine that you're installing theSQL Server on
2. Next

Note: If we were installing a new SQL Server, we would leave the 'Performa 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

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

1. Check Integration Services
2. Next

Note: Best practice is to not use the default account to run any of the SQL Serverservices. 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

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

1. Verify Summary
2. Install

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

1. Verify install was successful
2. Review log file

1. Close setup screen

Create Integration Services Catalog

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

OpenSQL Server Management Studio (SSMS) and connect to the SQL Server we justinstalled SSIS on.

1. Expand server dropdown
2. Right click on 'Integration Services Catalog'
3. 'Create Catalog…' to open the Catalog Creation Wizard

1. Check 'Enable CLR Integration' checkbox to enable Common LanguageRuntime (CLR) so SSIS can run CLR stored procedures
2. Check 'Enable automatic execution of Integration Services stored procedureat SQL Server startup' so thestate of operationsfor 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

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

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

First, run the following to verify cleanup is enabled and to obtain the numberof 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 'Enableautomatic execution of Integration Services stored procedure at SQL Server startup'during the engine install and RETENTION_WINDOW = default = 365 days.

The default RETENTION_WINDOW is probably OK if the SSIS server won't bethat active. However, if it will be active and you're concerned with the SSISDBdatabase getting too large and there is no requirement to keep 365 days of historyit'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 changeSELECT [property_name], [property_value]FROM [SSISDB].[internal].[catalog_properties]WHERE property_name IN ('RETENTION_WINDOW');

And we see it's been changed to 90.

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 getyou started with creating and executing SSIS Packages:

• SQL Server Integration Services SSIS 2016 Tutorial
• Running a SSIS Package from SQL Server Agent Using a Proxy Account
• SQL Server Integration Services SSIS Performance Tuning Techniques
• Managing the size of the SQL Server SSIS catalog database
• Extract, Import and Migrate SSIS Project
• SQL Server Integration Services Development Tips

About the author

Article Last Updated: 2023-06-28