SQL Server Integration Services (SSIS) is a powerful data integration tool that allows you to automate complex data migration and transformation tasks. In simple steps, this guide will show you how to migrate the SSISDB catalog from one server to another using SSIS Catalog Migration Wizard.
What is SSIS Catalog Migration Wizard?
SSIS Catalog Migration Wizard helps you migrate the full or part of the SSIS catalog to another SQL server. You can move the SSIS catalog to another server between any version of SQL Server (2012, 2014, 2016, 2017, 2019, 2022, Azure SSIS integration runtime, or Azure SQL managed instance). Apart from one-time migrations, this tool is designed to help you easily manage day-to-day SSIS catalog activities read more.
To start, launch the wizard from your preferred location.
Choose Source
Choose the SQL Server radio button and then provide the SQL Server instance name. To perform operations on the SSIS catalog, we have to use Windows Authentication, and the user running this should have the ssis_admin role.
Choose Target
Choose the SQLServer radio button as shown in the below image. Provide the target SQL Server host name.
Select SSISDB Catalog Items to Migrate
Choose the catalog items from the treeview that you wish to migrate.
Replace your environment variable values.
Configure the key-value pairs you want to replace in the environment variable and parameter values. Suggest button will list suggestions for replacing values based on the selected Replace Scope. Click on the Ref column to view the affected environment variables and parameters, as shown in the image below.
Complete the Wizard
In the end, you can view the summary based on your selections. Review the deployment summary. And if everything looks ok, click Finish.
Any warning or error during the migration gets shown next to the respective folder in the Result column tooltip of the grid. The success items are all noted as “Passed” in the above image.
Installation
SSIS Catalog Migration Wizard can be installed as an extension to:
1. Visual Studio 2017, Visual Studio 2019 & Visual Studio 2022.
2. SSMS 18, SSMS 19.
3. Standalone tool.
More details are available in this tutorial.
Pro tips:
1. This article describes how to Open SSIS Packages from ispac file.
2. Restoring SSISDB to another server can be problematic as the SSISDB database design differs in higher versions of SSISDB (Integration services catalog). If you want to create a new SSIS Catalog, follow this article.