From SQL Server 2012 onwards, Microsoft introduced a new deployment model in SSIS called the project deployment model. This new model has many benefits in managing SSIS project deployments, executions, and configurations. Every new version of SQL Server also upgrades the SSIS Catalog with new features. In this article, you will learn, how to copy SSIS Environment variables to another server using SSIS Catalog Migration Wizard in just a few clicks.
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(preview), Azure SSIS integration runtime, or Azure SQL managed instance). Apart from one-time migrations, this tool is designed to help you manage day-to-day SSIS catalog activities with ease Read more.
Demo – Copy SSIS Environment variables to another server
Launch SSIS Catalog Migration Wizard from your installed location and follow the below simple steps to migrate SSIS environment variables from one SQL server to another.
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 SQL Server radio button and then provide the Target SQL Server instance name. Click Next.
Select SSIS environment(s) to migrate
From the source catalog view, select the desired SSIS environment that you need to migrate to the target SSIS catalog. You can also select multiple SSIS environments. Click Next.
Customize the target folder name.
If you wish to change the target folder name of the environment, you can edit the target folder name on this screen. This step is optional. Click Next.
Replace environment variable values
On this screen, you can replace environment variable values. As shown in the image below, you can replace the current value with the new one.
Review your choices
Review your choices made in all the above steps. If everything looks ok, click Finish.
Monitor the progress
Monitor the progress. If there is no error or warning, you should be able to see the SSIS environment in the target SSISDB.
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. Learn how to migrate SSISDB across different networks in just a few clicks.