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 SSIS projects from SQL Server in bulk using SSIS Catalog Migration Wizard in just a few clicks.
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(preview), Azure SSIS integration runtime, or Azure SQL managed instance). Apart from one-time migrations, this tool is designed to help you manage day-to-day SSIS catalog activities with ease Read more.
How to Export SSIS Project from SSISDB in SQL Server
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 SSIS Catalog items 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
Review your choices made in all the above steps. If everything looks ok, click Finish.
Monitor the progress
Monitor the progress. If there is no error or warning, you should be able to see the export file on the selected target folder.
Extract the .scmw export file to get the exported SSIS projects
Automation
If you want to automate the export SSIS project from SQL Server, use SSIS Catalog Migration Wizard command-line utility. You can run the below command from the command prompt or schedule it in any scheduler. Read more about it here.
"C:\program files (x86)\microsoft sql server management studio 18\common7\ide\extensions\ssis cataloger\SSIS.Cataloger.Pro.exe" /st:0 /ssn:LAPTOP-R9A0KU50 /tt:2 /scmwfp:"D:\SCMW exports\LAPTOP-R9A0KU50_2022-10-23_04-42-17.scmw"
We have seen how to Export SSIS project from SQL Server in bulk 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.
Trusted by leading brands worldwide
