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, I will describe how to export and import SSISDB using SSIS Catalog Migration Wizard in just a few clicks.
The need for this activity might arise when:
- When you want to migrate SSISDB from one server to another but in a different network (without direct connection).
- When you want to migrate SSISDB from one server to another server that is under a different domain account.
All the above types of migrations can be achieved in just a few clicks using SSIS Catalog Migration Wizard by the below steps:
- Export SSIS Catalog items from the source server (SSISDB) into the .scmw file.
- Copy the .scmw export file to the target server.
- Import the .scmw file to the target server (SSISDB).
What is SSIS Catalog Migration Wizard
The 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.
Export SSISDB from Source SSISDB
Launch the SSIS Catalog Migration Wizard from your installed location and follow the below simple steps to export multiple SSIS projects from SSISDB in just a few clicks.
Choose the Source SSISDB
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 the Target
Choose the SCMW file radio button and then the path of the local Windows folder where we need to export the SSSI projects. Click Next.
Select the SSIS Catalog item to migrate.
From the source catalog view, select the desired SSIS projects that you need to export. Click Next.
Customize the target folder name and replace it. Stepsonment variable values steps are not applicable; hence click Next to skip these steps.
Review your choices and click Finish to export.
Review your choices made in all the above steps. If everything looks ok, click Finish.
Copy the .scmw export file to the target server.
Copy the .scmw export file to the target server or computer where you have access to the target SQL server (SSISDB).
Import the .scmw file to the target server (SSISDB).
Launch the SSIS Catalog Migration Wizard from your installed location and follow the below simple steps to import the .scmw file to the target SQL server (SSISDB).
Choose Source (scmw file)
Select the Source as scmw type and browse the export file using the file browser.
Choose the Target
Choose the target SQL server to import the scmw file and click Next.
Select the SSIS Catalog item to migrate.
From the source catalog view, select the desired SSIS projects that you need to export. Click Next.
Replace environment variable and parameter 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.
Review your choices and click Finish to export.
Review your choices made in all the above steps. If everything looks ok, click Finish. Once the process finishes, you should see selected catalog items in the target SSISDB.
Installation
SSIS Catalog Migration Wizard can be installed as an extension to:
- Visual Studio 2017, Visual Studio 2019 & Visual Studio 2022.
- SSMS 18, SSMS 19.
You can also install this wizard as a standalone tool if you have none of the above tools. More details are available in this tutorial.
The extension for Visual Studio 2017 /2019/2022 is available at Visual Studio Marketplace. This can also be downloaded directly from the Visual Studio 2017/2019/2022 Tools and Extensions and Updates. For other tools, you can get it from the product website.
