Installing SQL Server Integration Services
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.
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
SSIS can be installed along with or added to an existing install of SQL Server.
- To start the install, double click setup.exe on your installation media and the first screen will open
The SQL Server Installation Center screen has links to a number of related tools and is where we start the install.
- Choose 'Installation' from the list on the left side to go to the next screen
This step is the same whether we're doing a new SQL Server installation or adding to an existing one.
- Choose 'New SQL Server stand-alone installation or add features to an existing installation' from the list on the right side
- Check off 'I accept the license terms andů'
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.
- Check 'Use Microsoft Update to check for updates' if you want to automatically check, otherwise leave unchecked
- 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
Note: If we were installing a new SQL Server, we would leave the 'Perform a new installation of SQL Server 2019' radio button selected.
- 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
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.
- Check Integration Services
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
- Choose service account to run the SSIS service
- Verify 'Startup Type' is Automatic
We have the opportunity to review a summary of what we're installing here.
- Verify Summary
At this point the installation is done and we now have SSIS installed.
- Verify install was successful
- Review log file
- Close setup screen
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.
- Expand server dropdown
- Right click on 'Integration Services Catalog'
- 'Create Catalog…' to open the Catalog Creation Wizard
- Check 'Enable CLR Integration' checkbox to enable Common Language Runtime (CLR) so SSIS can run CLR stored procedures
- 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.
- Enter strong password (and be sure save in your password manager)
- Retype password
- OK to create Catalog
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.
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.
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.
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.
Now that we have SSIS installed and catalog created here are some links to get you 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
Last Updated: 2020-11-12
About the author
View all my tips