SQL Server Integration Services (SSIS) is available in preview in SQL Server Management Studio v21.2.5. Here’s how to configure SSMS get started using the SSIS Catalog!
Install or Update, then Modify, SSMS
If you have not installed SQL Server Management Studio v21, you can grab the latest installation bits here.
If you have already installed SQL Server Management Studio v21, search for the Visual Studio Installer:
When Visual Studio Installer opens, click the “Update” button for SQL Server Management Studio 21 to update SSMS to the latest version.
Modify SSMS
Before adding SSIS to SSMS, Object Explorer appears similar to:
After installing or updating SSMS v21, click the “Modify” button in the “SQL Server Management Studio 21” tile in Visual Studio Installer to modify SSMS:
To add “SQL Server Integration Services (SSIS) (Preview),” select the “Business Intelligence” workload, expand the Business Intelligence category in the Installation Details list, and then check the “SQL Server Integration Services (SSIS) (Preview)” checkbox:
You may now use SSMS v21 to create a new SSIS Catalog.
Create an SSIS Catalog in SSMS v21
To begin, open Object Explorer, right-click “Integration Services Catalogs” node, and then click “Create Catalog…”:
When the Catalog Creation Wizard displays:
- Check the “Enable CLR Integration” checkbox. Some SSIS Catalog functionality resides in the .Net Framework, and interaction with the .Net Framework requires CLR (Common Language Runtime) integration.
- Although optional, I recommend checking the “Enable automatic execution of Integration Services stored procedure at SQL Server startup” checkbox when anyone creates an SSIS Catalog.
- Enter a password for encryption in the SSIS Catalog. Save this password in a secure location! You may need it one day.
- Re-enter the same password.
- Click the “OK” button to create the SSIS Catalog:
If all goes as hoped, SSMS v21’s Object Explorer will now display an SSIS Catalog named “SSISDB” along with its database of the same name:
You are now ready to manage SSIS using SSMS v21!
Enjoy!
Want to Learn More about SSIS?
Sign up for SSIS Premium (on sale now) to access recorded SSIS training!
Subscribers may access the following courses for a full year:
Register for SSIS Premium today!
:{>
I had to reinstall the Integration Services extension for VS 2022 after I modified my SSMS 21 to include the Integration Services pieces.
I kept getting an error related to my connection managers:
Error loading ‘: Unable to create the type with the name ‘OLEDB’
My previous comment was not quite correct.
SSMS 21 SSIS (preview) cannot be installed on my computer, or it breaks the Visual Studio 2022 SSIS extension. I get the “Error loading: Unable to create the type with the name ‘OLEDB'” error message related to my connection managers. I then had to reinstall SSIS for VS 2022 to get them to work.