Skip to content

Instantly share code, notes, and snippets.

@schwartzmx
Last active August 12, 2016 19:16
Show Gist options
  • Save schwartzmx/2da74e9364829ebee3eca2b419c9b99e to your computer and use it in GitHub Desktop.
Save schwartzmx/2da74e9364829ebee3eca2b419c9b99e to your computer and use it in GitHub Desktop.
Transfer SSIS Folders and Projects from a source server to a destination server.
<#
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"
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment