Export SSIS project from SQL Server

Export ssis project from ssisdb

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.

Export SSIS project from SQL Server

Select SSIS projects(s) to migrate

From the source catalog view, select the desired SSIS projects that you need to export. Click Next.

Export SSIS project from SQL Server

Customize the target folder name and replace environment variable values steps are not applicable in this case and hence keep 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

Export SSIS project from SQL Server

Automation

If you want to automate the export SSIS project from SQL Server, you can also 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.

Kunal Rathi

Been working in the data engineering and analytics space for over a decade. Helping customers transform their data into insights, Cloud & DevOps enthusiast.