SQL Server Integration Services (SSIS) is a crucial component of the Microsoft Business Intelligence (MSBI) stack. It serves as the Extract, Transform, and Load (ETL) tool that consolidates data from various sources, transforms it, and loads it into a destination. If you’re tasked with installing SSIS as part of a SQL Server installation or adding it to an existing one, this step-by-step guide will walk you through the process.

Minimum Hardware and Software Requirements

Before proceeding with the installation, ensure that your system meets the minimum hardware and software requirements:

• 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

To begin the installation, follow these steps:

1. Double-click on setup.exe from your installation media to launch the SQL Server Installation Center.
2. Choose “Installation” from the list on the left side.
3. Select “New SQL Server stand-alone installation or add features to an existing installation” from the list on the right side.
4. Check the box for “I accept the license terms and…” and click “Next”.
5. If desired, check “Use Microsoft Update to check for updates” to enable automatic updates. Otherwise, leave it unchecked and click “Next”.
6. If the Windows Firewall service is running on the machine, click on the provided link to learn which ports need to be open. Then, click “Next”.
7. Select the “Add features to an existing instance of SQL Server 2019” radio button and choose the instance you want to add SSIS to from the dropdown menu. Click “Next”.
8. Check the box for “Integration Services” and click “Next”.
9. Choose the service account to run the SSIS service and verify that the “Startup Type” is set to “Automatic”.
10. Review the installation summary and click “Install”.
11. Once the installation is complete, verify that SSIS has been successfully installed and review the log file. Then, close the setup screen.

Create Integration Services Catalog

After the base installation of the SSIS engine, you need to manually create the Integration Services Catalog. This catalog is where SSIS projects are deployed and managed. Follow these steps:

1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server where SSIS is installed.
2. Expand the server dropdown and right-click on “Integration Services Catalog”.
3. Select “Create Catalog…” to open the Catalog Creation Wizard.
4. Check the “Enable CLR Integration” checkbox to enable Common Language Runtime (CLR) for running CLR stored procedures.
5. Check the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox to ensure the state of operations for the SSISDB catalog is performed when the service is started.
6. Enter a strong password for the catalog and make sure to save it in your password manager.
7. Click “OK” to create the catalog.

Once the catalog is created, you will see the SSISDB database and Integration Services Catalog in the SQL Server Management Studio.

Managing SSISDB Database Size

If your SSIS server is expected to be active, you may want to manage the size of the SSISDB database by adjusting the SSIS history retention. By default, the retention window is set to 365 days. To change it, follow these steps:

1. Run the following query to verify if cleanup is enabled and to obtain the current number of retention days:
   SELECT [property_name], [property_value]
FROM [SSISDB].[internal].[catalog_properties]
WHERE property_name IN ('OPERATION_CLEANUP_ENABLED', 'RETENTION_WINDOW');
2. If the cleanup is enabled and you want to change the retention window, execute the following command:
   EXEC [SSISDB].[catalog].[configure_catalog] RETENTION_WINDOW, 90;
   This example changes the retention window to 90 days. Adjust the value as per your requirements.
3. Run the previous query again to verify the change:
   SELECT [property_name], [property_value]
FROM [SSISDB].[internal].[catalog_properties]
WHERE property_name IN ('RETENTION_WINDOW');

Now, the retention window should reflect the new value you set.

Conclusion

Congratulations! You have successfully installed SQL Server Integration Services (SSIS) and created the Integration Services Catalog. You are now ready to deploy and manage SSIS projects. Remember to regularly monitor the size of the SSISDB database and adjust the retention window as needed to optimize performance and storage.

For more information about SQL Server versions and cumulative updates, refer to the official Microsoft documentation.

Article Last Updated: 2023-06-28