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 terms of managing SSIS project deployments, executions, and configurations. With every new version of SQL Server, the SSIS Catalog is also getting upgraded with new features. In this article, I will describe how to migrate the SSIS catalog from one server to another using the SSIS Catalog Migration Wizard.
The SSIS Integration Services Catalog consists of the following artifacts:
- SSIS Builds (.ispac files) – Contains several SSIS packages with project and package parameters.
- Environments – Stores configurations of SSIS projects. These variables are used to configure the SSIS project and package parameters of SSIS projects.
The need for this activity might arise when:
- you are migrating your SQL server to a new host OS.
- upgrading the SQL server to a newer version on a different machine.
- setting up a new SSIS environment from the existing setup, etc.
This quick utility will help you migrate SSIS catalog from one server to another in just a few clicks.
SSIS Catalog Migration Wizard
Once installed, you can launch the SSIS Catalog Migration Wizard from SQL Server Management Studio (SSMS) 2018, Visual Studio 2017, or Visual Studio 2019. Alternatively, you can use it as a standalone tool if you don’t have either of the tools.
The wizard supports the following source and target types.
- SSIS for SQL Server – SSIS catalog enabled in SQL Server.
- SSIS for Azure Data Factory – This is used when you want to run SSIS packages in Azure using Azure data factory pipelines.
- File System – SSIS Catalog exported to the file system. This can be used as a staged migration when you don’t have access to source and target SQL Server connections at the same time.
How to install
Download and follow the instructions from the Visual Studio Marketplace
Alternatively, if you have Visual Studio 2017 or 2019, you can download it from Extensions and Updates under the Tools menu item.
A good part of it with Visual Studio is that you automatically get an update installed in Visual Studio whenever a newer version of the wizard is available in the marketplace. Isn’t that great?
Very easy and useful! Thank you!Leonard Olteanu – Visual Studio Marketplace
Great tool for migrating SSIS projects and environments between environments and SSIS versionsTonny Almgren-Storm – Visual Studio Marketplace
At our place, we are always creating and deploying new solutions from qa to prod and this is always a very time-consuming job for my junior DBA’s. I did have a stored proc I found online that allowed me to script out the variable names and re-execute them in prod, but it failed to do any of the re-mappings we needed. Also, my junior DBA’s found it confusing and often didn’t leverage it. I never heard of this tool before. This will be much easier for my team to follow. This tool is an absolute game-changer for my team. Thanks a lot for sharing this.citrowske – sqlservercentral.com
Demo – Migrate SSISDB from one server to another
We’ll see a quick demo on how SSIS Catalog Migration Wizard migrates on-premises SSIS Catalog in SQL Server to SSIS in Azure Data Factory in just a few clicks. Launch the wizard from your preferred location.
Choose SSIS in SQL Server from Source Type drop-down. Provide the SQL Server instance name. To perform operations on the SSIS catalog, we have to use windows authentication. And the user should have the ssis_admin role.
Choose SSIS in Azure Data Factory from the Target Type drop-down. Provide Azure SQL Server hostname, admin SQL server authentication user name, and password.
Note – This utility only supports SQL authentication for Azure SSIS at the moment.
Select SSISDB Catalog Folders to Migrate
Choose the catalog folders from the listview that you want to migrate.
At the bottom, choose the catalog options:
- Migrate Projects – Select the checkbox if you want to migrate SSIS projects (.ispac) files from the selected list of folders.
- Migrate Environments, Project, and Package parameter configurations – Select the checkbox if you want to migrate catalog Environments. This will apply environment references to SSIS projects and environment variables to parameter mapping. It also includes project and package parameters with default values set in the source SSISDB.
- Delete selected folders from the source Catalog – Select the checkbox if you want to delete selected Catalog folders from the source server.
While you are on your way, would you like to replace your environment variable values?
If you wish to replace part of the environment variables, project, or package parameter values with new values, configure the replacement rules on this screen.
As shown in the image above, SSIS Catalog Migration Wizard replaces all occurrences of substring Data Source=server1 with Data Source=server2, User ID=user1 with User ID=user2, and C:\ETL\Folder with D:\ETL\Folder in all the environment variables and project and package parameter values.
This is useful when you are setting up a parallel environment for your ETL workload and some configuration is different in the target environment. However, this is an optional step. If you don’t want to replace anything, you can skip this step and click Next.
Complete the Wizard
Review the deployment summary. And if everything looks ok, click Finish.
Monitor the migration
Any warning or error during the migration gets available against the respective folder in the Result column tooltip of the grid as shown in the above image.
Note – Sensitive environment variables, project, or package parameter values are encrypted in SSISDB with the master key. Hence, the wizard will not migrate such values. However, it will list such variable names in the Result column tooltip with column value as “Warning”. You can also save migration reports in txt format by clicking on Save Report.
I would love to hear from you about your experience using this tool.