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 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 SQL Server (SSISDB) into the .scmw file.
- Copy the .scmw export file to the target server.
- Import the .scmw file to the target SQL Server (SSISDB).
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.
1. Export SSIS Catalog items from the source SQL Server into the .scmw file.
Launch 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 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 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 environment 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.
2. 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).
3. Import the .scmw file to the target SQL Server.
Launch 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 you have exported in step1.
Choose 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.
We have seen how to export and import SSIS Catalog in just a few clicks using SSIS Catalog Migration Wizard.
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. If you need to delete SSIS Catalog items in bulk, then select the items you want to delete from source SQL Server. Select target as scmw file and use Migration type as ‘move’. This will export selected catalog items into .scmw file and also delete them from source SQL Server SSISDB.
2. Learn how to copy SSIS environment from one SQL Server to another in just a few clicks.