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 Data Factory/ Azure SQL Database, 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. It also provides a command-line utility to automate routine deployments or migrations.
SCMW file type
SCMW file type is an export format of the SSIS Catalog Migration Wizard. It can be a backup or a stage deployment file for cross-server migrations.
Associate SCMW file type to this application
To let windows recognize the scmw file type, register this application. Check the checkbox ‘Associate SCMW file type to this application on Choose Target screen – target type scmw.
Note: This is only a one-time setting, and you may need to do this again if you wish to change the application path.
Learn how to export and import SSISDB using SCMW export.
Select SSISDB Catalog items.
Compare source & target.
Compare source and target and choose to migrate only what has changed.
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 are identical in the source and target.
You can download the comparison report in CSV format. The report shows Project’s latest deployment date, SSIS package count, project parameter count, and environment variable count comparison.
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 if you wish to delete the source catalog after the migration is done. The default is ‘copy.’
Migrate explicit permissions
Catalog folders, projects, and environments have explicit permissions. If you want to migrate it to target, check the checkbox ‘Migrate explicit permissions on this screen.
Inspect the source catalog for issues.
Inspect the source catalog for issues in environment variable configuration before the migration.
Customize folder mapping
Easily map source and target folder names. Wizard populates all selected folder names in Source and Target folder columns. If you’d like to customize the folders, you can just edit the value in the ‘Target Folder’ column. This step is optional except for in-place SSIS migrations.
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 shown in the image below.
Advanced
Click on Advanced to manage replace settings.
Replace Scope
There are three types of replace scopes as shown in the below image.
EnvironmentVariables
Applicable environment variable values will be replaced in the target based on configured replace rule. The Suggestion feature will include all environment variables from selected environments for replacement suggestions.
ProjectParameters
Applicable project parameter values will be replaced in the target based on configured replace rule. The Suggestion feature will include all project parameters from selected projects for replacement suggestions.
PackageParameters
Applicable package parameter values will be replaced in the target based on configured replace rule. The Suggestion feature will include all package parameters from selected projects for replacement suggestions.
Suggestion Method
Two types of suggestion methods provide recommendations for environment variables, and parameter values replace rules.
RegexSubstring
This is the default suggestion method. It uses (?[^=;]+)=(?[^;]+) regular expression to list replace suggestions based on selected replace scope (Project, package, environment). This method is recommended when replacing part of database connection strings with new values. When this suggest method is selected, Wizard will replace all occurrences of the key with value in the selected replace scope.
ActualValue
This method lists all values of environment variables, projects, and package parameters in replace suggestions based on the selected replace scope. When this suggest method is selected, Wizard will replace the exact match of the environment variable, project, or package parameter with the value in the selected replace scope.
Overwrite environment variable and parameter server-side values.
‘Overwrite environment variable and parameter server side values’ will:
- recreate the environment variables that are already present in the target based on the latest source environment variables,
- overwrite server-side default values if already set in target project and package parameters. These values appear in bold and are meant to be specific to an SSIS catalog server.
It is recommended to keep this checkbox unchecked for incremental migration of the SSIS catalog from one server to another. In this case, the wizard will only migrate new parameter design values and environment variables that do not exist in the target.
Export sensitive data
This option applies when exporting SSIS Catalog to the SCMW file. If you want to export sensitive variables and parameter values in the SSIS catalog, check the checkbox ‘Export sensitive data’.
Note: SCMW export file is not encrypted. If you set the checkbox ‘Export sensitive data’, sensitive information will be stored in the export file as free text.
Automation
With the command-line utility, we can now automate routine migrations in a matter of a few clicks. The migration script can be integrated with any scheduler or CI-CD tool.
Command-Line Syntax
SSISCataloger.Pro.exe initiates the migration using the parameters specified on the command line.
Parameters
Below is a table summarizing the parameters for command-line utility.
| Parameter | Usage | Description |
|---|---|---|
| st | Mandatory | Source type. 0 – SQL Server,1 – Azure SSIS IR, 2 – SCMW file. |
| ssn | Mandatory when st = 0 | Source SQL Server instance name. |
| su | Mandatory when st = 1 | Source user name. Admin user name for Azure SQL server containing SSIS IR. |
| sp | Mandatory when st = 1 |
Source password. Admin user password for Azure SQL server containing SSIS IR. |
| tt | Mandatory | Target type. 0 – SQL Server,1 – Azure SSIS IR, 2 – SCMW file. |
| tsn | Mandatory when tt = 0 | Target SQL Server instance name. |
| tu | Mandatory when st = 1 | Target user name. Admin user name for Azure SQL server containing SSIS IR. |
| tp | Mandatory when st = 1 | Target password. Admin user password for Azure SQL server containing SSIS IR. |
| scmwfp | Mandatory when st or tt = 2 | The full path of the SCMW file. |
| opr | Optional | Operation. Specify which operation to perform. Possible values are ‘migrate’ and ‘inspect’. The default is ‘migrate’. This parameter is in preview. |
| items | Optional | Specify catalog items to be migrated in JSON format. e.g.: “[{“FolderName”:”Name of the folder”,”Projects”:[“testUC”],”Environments”:[“env1″]}]” When not specified, entire catalog items from the source will be migrated to the target. |
| mt | Optional | Migration type. Possible values are ‘copy’ and ‘move’. The default is ‘copy’. When ‘move’ is specified, selected catalog items will be deleted from the source during the migration |
| fm | Optional | Source and target folder mapping. e.g.: If you wish to migrate ‘Azure test’ folder items to ‘Azure prod’, specify fm as “{“Azure test”:”Azure prod”}” |
| oev | Optional | Overwrite environment variables. Possible values are ‘true’ and ‘false’. Default is ‘false’. When set to ‘true’, existing environment variables in the target will be recreated based on source variables. |
| lfd | Optional | Log file directory. This is used to record execution logs and errors during command-line migrations. When not specified, the application will write log at the user’s default location. |
Usage examples
Add the folder path of the SSIS.Cataloger.Pro.exe file in the Windows system environment variable Path. Alternatively, we can specify the full path of SSIS.Cataloger.exe in the command prompt.
Export the entire SSIS catalog from SQL Server SSISDB to SCMW file.
"D:SSIS Cataloger.ProSSIS.Cataloger.Pro.exe" /st:0 /ssn:. /tt:2 /scmwfp:"D:SCMW exportsExport_2021-07-18_06-49-17.scmw" Import specific SSIS catalog artifacts from the SCMW file to SQL Server SSISDB and rename a folder in the target.
SSIS.Cataloger.Pro.exe /st:2 /scmwfp:"D:SCMW exportsExport_2021-07-18_06-49-17.scmw" /tt:0 /tsn:TargetServer /items:"[{"FolderName":"Azure test","Projects":[],"Environments":["env1"]},{"FolderName":"AzureDevOpsDeployment","Projects":["testUC"],"Environments":[]},{"FolderName":"Sales","Projects":["sales-stg2"],"Environments":[]}]" /fm:"{"Azure Test":"Azure Prod"}"Migrate specific SSIS catalog artifacts from one SQL server instance to another with log file at a custom location.
SSIS.Cataloger.Pro.exe /st:0 /ssn:SourceServer /tt:0 /tsn:TargetServer /items:"[{"FolderName":"Azure test","Projects":[],"Environments":["env1"]},{"FolderName":"AzureDevOpsDeployment","Projects":["testUC"],"Environments":[]},{"FolderName":"Sales","Projects":["sales-stg2"],"Environments":[]}]" /lfd:"D:SCMW exports"Inspect the SSIS catalog for environment configuration issues.
"D:SSIS Cataloger.ProSSIS.Cataloger.Pro.exe" /st:0 /ssn:LAPTOP-R9A0KU50 /opr:inspectExit codes
0 = Success
-1 = Failure
Validate projects after the migration is done
Enable this checkbox to validate all the deployed projects automatically. This process will include environemnt_scope = 'A' for the validation process. This means each project will be validated against all the assigned environment references.
Use 32-bit runtime
Use this setting if you want to validate projects using 32-bit runtime.
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.
Note: Create a fresh SSISDB in the target Integration Services Catalog before the migration if it does not already exists. Restoring an existing SSISDB on the target SQL Server may result in unexpected errors during or after the migrations.
Watch this short video to know more.