Getting started
We will now see how SSIS Catalog Migration Wizard migrates SSISDB from one SQL Server to another in just a few clicks.
Table of contents
- Welcome Screen
- Choose Source
- Choose Target
- Select SSISDB Catalog Items to Migrate
- Customize Folder Mapping
- Replace Environment Variable and Parameter Values
- Overwrite Environment Variable Values
- Export Sensitive Data
- Complete the Wizard
- Monitor the Migration
To start, launch the wizard from your preferred location.
Welcome Screen
Click Next to continue.
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 Azure data factory radio button for the target. Provide the Azure SQL Server hostname, admin SQL server authentication user name, and password.
This utility only supports SQL authentication for the Azure SQL Server connection.
Select SSISDB Catalog Items to Migrate
Choose the catalog items from the Treeview.
Compare source & target
Compare source and target and choose to migrate only what has changed. For example, in the above image:
- Green items are present in the source and not present in the target,
- Red items mean source and target items are not the same.
- Unmodified items are displayed in the original color, and these objects are identical in the source and target
Migrate only configuration
You can use this option to migrate only the project references and parameter values. Simply select the checkbox to migrate only the configuration part without the project code (.ispac file). Keep in mind that if the source project is not present in the target, both the project and its configuration will be migrated.
Migration type
Migration types are copy and move. Select the move radio button to delete the source catalog after the migration. The default type is copy.
Migrate explicit permissions
Catalog folders, projects, and environments have explicit permissions. To migrate these permissions to the target, check the ‘Migrate explicit permissions’ checkbox screen.
Customize Folder Mapping
Easily map source and target its permissions’ checkbox folder names. The wizard populates all selected folder names in the Source and Target folder columns. If you wish to customize the folders, edit the value in the ‘Target Folder’ column.
For instance, as shown in the above image, the wizard will copy the content of the source catalog folder Pqr to the target catalog folder Abc. This step is optional.
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.
Perhaps, this is useful when setting up a parallel environment for your ETL workload, and some configurations are different in the target environment. This configuration is optional.
Overwrite Environment Variable Values
The Overwrite environment variable values setting recreates environment variables and parameter default values in the target.
Using this setting will recreate target SSIS environment variables. You would loose existing target environment variable values in this case.
Export Sensitive Data
This setting applies to exporting SSIS Catalog items to the SCMW file. SCMW export file is not encrypted. When the Export sensitive data setting is set, the wizard exports sensitive information in the export file as free text.
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 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.