Transfer SSIS Folders and Projects from a source server to a destination server.

SQLServer_SSIS-Transfer.ps1

<#
    Transfer SSIS Folders and Projects from a source server to a destination server.
    
    Mostly taken from http://widba.blogspot.com/2013/02/moving-SSIS-projects-in-sql-server-2012.html with some added functionality for copying ALL SSIS packages and folders to the destination

    Note this does have to use SSPI Integrated Security for copying from the Source to Dest

    Author: Phil Schwartz
#>

$SourceServer = "Src-SQL"  
$DestinationServer = "Dest-SQL"  
 
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"       
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")
 
$connString = "Server=$SourceServer;Initial Catalog=master;Integrated Security=SSPI;"
$destconnString = "Server=$DestinationServer;Initial Catalog=master;Integrated Security=SSPI;"
$conn = New-Object System.Data.SqlClient.SqlConnection $connString       
$destconn = New-Object System.Data.SqlClient.SqlConnection $destconnString

# Create the Integration Services object       
$SSIS = New-Object $ISNamespace".IntegrationServices" $conn
$destSSIS = New-Object $ISNamespace".IntegrationServices" $destconn

#Grab the SSISDB Catalog  
$Catalog = $SSIS.Catalogs | ? { $_.Name -eq "SSISDB" }  
$destCatalog = $destSSIS.Catalogs | ? { $_.Name -eq "SSISDB" }  

# Grab all folders for iteration
$sourceFolders = $Catalog.Folders
$destFolders = $destCatalog.Folders

# If the destination folders are missing - create them
foreach ($folder in $sourceFolders) {
    if($($destFolders.Name) -notcontains $($folder.Name))
    {  
        $destFolder = New-Object $ISNamespace".CatalogFolder" ($destCatalog, $folder.Name, $folder.Description)       
        $destFolder.Create()
    } 
}

# Refresh
$destFolders.Alter()
$destFolders.Refresh()

# For each folder, deploy all of the projects within it to the dest
foreach ($curFolder in $sourceFolders ) {
    Write-Host "Starting Project Deployments for folder: $($curFolder.Name)"  -ForegroundColor "Yellow"
    foreach($proj in $curFolder.Projects)  
    {  
        if($conn.State -eq "Closed") { $conn.Open() };  
        $cmd = New-Object System.Data.SqlClient.SqlCommand  
        $cmd.CommandType = "StoredProcedure"  
        $cmd.connection = $conn  
        $cmd.CommandText = "SSISDB.Catalog.get_project"  
        $cmd.Parameters.Add("@folder_name",$curFolder.Name) | out-null;  
        $cmd.Parameters.Add("@project_name",$proj.Name) | out-null;  
        [byte[]]$results = $cmd.ExecuteScalar();  
        if($results -ne $null) {  
            $destFolder = $destFolders | ? { $_.Name -eq $curFolder.Name }
            $deployedProject = $destFolder.DeployProject($proj.Name,$results)  
            Write-Host "    Project: $($proj.Name) - DeployStatus: $($deployedProject.Status)."  -ForegroundColor "Green"
        }  
        else {  
            Write-Host "    Failed deploying $($proj.Name) from folder $($currFolder.Name)." -ForegroundColor "Red"  
        }  
    }  
}