dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Copy-SqlSsisCatalog

This command migrates Folders, SSIS projects, and environments from one SQL Server to another.

By default, all folders, projects, and environments are copied. The Project parameter can be specified to copy only one project, if desired.
The parameters get more granular from the Folder level i.e. specifying folder will only deploy projects/environments from within that folder.

Screenshots

Output when SSIS catalog already exists

copy-sqlssiscatalog1

If SSIS is not yet setup, the script can set it up for you

copy-sqlssiscatalog2

Examples

To copy all folders, environments and all ssis Projects from sqlserver2014a to sqlcluster, using Windows credentials. If folders with the same name exist on the destination they will be skipped, but projects will be redeployed.

Copy-SqlSsisCatalog -Source sqlserver2014a -Destination sqlcluster

To copy a single Project, the Archive_Tables Project from sqlserver2014a to sqlcluster, using SQL credentials for sqlserver2014a and Windows credentials for sqlcluster. If a Project with the same name exists on sqlcluster, it will be deleted and recreated because -Force was used.

Copy-SqlSsisCatalog -Source sqlserver2014a -Destination sqlcluster -Project Archive_Tables -SourceSqlCredential $cred -Force

To show what would happen if the command were executed using force.

Copy-SqlSsisCatalog -Source sqlserver2014a -Destination sqlcluster -WhatIf -Force

To deploy an entire SSIS catalog to an instance without a destination catalog. Passing -CreateCatalogPassword will bypass any user prompts for creating the destination catalog.

$SecurePW = Read-Host “Enter password” -AsSecureString
Copy-SqlSsisCatalog -Source sqlserver2014a -Destination sqlcluster -CreateCatalogPassword $SecurePW

Author

This command was created by Phil Schwartz. You can find Phil Schwartz blogging and on LinkedIn.

Get-Help

From PowerShell, execute Get-Help Copy-SqlSsisCatalog -Detailed for more information on this function.

Source Code

Want to see the source code? View Copy-SqlSsisCatalog.ps1 on GitHub
 

Related commands