Automating SSISDB Migration 2/4 – Exporting SSISDB Projects to .ispac with Powershell

Continuing from the first step of a legacy SSIS migration project – exporting MSDB packages – the next is to export SSISDB projects. As I mentioned in the previous post, Microsoft has included a handy wizard to do this for you since SQL 2016, however it might not be appropriate for you to use, as it wasn’t at my company – we were ditching a lot of of the existing projects, editing some of the others, and wanted a clean slate.

SSISDB projects, unlike their MSDB ancestors, are relatively sophisticated beasts and exported as .ispac (project) files. They can be exported easily via SSMS one at a time – but if you need to export hundreds, it’s not as simple as dumping out a VARBINARY conversion of column data.

Thanks to the SSIS .NET API it’s still pretty easy, though. We can access the SSIS instance, enumerate the folders and projects, then call the GetProjectBytes() function on each to get the .ispac data.

The below Powershell function from the Powershell SSIS repo I’m working on takes an instance name and output directory and outputs all of the projects as .ispac files, creating a subfolder structure in the output directory mirroring the SSISDB folders.

For example, for an SSISDB with three folders – QuarterlyReports, ChurnDataPointlessly, AwfulQueryGreatestHits, each with a Project called IntegrationServicesProject1 – and given an output dir of D:\DBA\, the result would be the following subfolders and files created;

D:\DBA\QuarterlyReports\IntegrationServicesProject1.ispac
D:\DBA\ChurnDataPointlessly\IntegrationServicesProject1.ispac
D:\DBA\AwfulQueryGreatestHits\IntegrationServicesProject1.ispac

The script assumes the user can authenticate via AD and has permissions to create the filesystem artefacts.

Github link

function Export-SSISProjectSSISDB
 {
 param
 (
     [Parameter(position=0, mandatory=$true)][string]$Instance,
     [Parameter(position=1, mandatory=$true)][string]$OutputDir
 )
 if(!(Test-Path $OutputDir))
 {
     Write-Output "Error - invalid path specified in OutputDir"
     
     return
 }
 $testquery = "SELECT COUNT(*) AS 'Result' FROM sys.databases WHERE name = 'SSISDB'"
 try
 {
     $result = (Invoke-Sqlcmd -ServerInstance $Instance -Query $testquery -ConnectionTimeout 5 -QueryTimeout 5 -ErrorAction Stop).Result
     
     if($result -eq 0)
     {
          Write-Output "Error - no SSISDB present on instance."
    
          return
     }
 }
 catch
 {
     Write-Output "Error - failure connecting to instance"
     return
 }
 
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null
 
 $SSISnamespace = "Microsoft.SqlServer.Management.IntegrationServices"
 
 $connstring = "Data source=$($Instance);Initial Catalog=master;Integrated Security=SSPI;"
 $sqlconn = New-Object System.Data.SqlClient.SqlConnection $connstring
 
 $SSIS = New-Object $SSISnamespace".IntegrationServices" $sqlconn

 $catalog = $SSIS.Catalogs["SSISDB"]
 foreach($folder in $catalog.Folders)
 {
     Set-Location -Path $outputdir

     New-Item -ItemType Directory -Name $folder.Name | Out-Null
  
     $folderpath = $outputdir + "\" + $folder.Name
 
     Set-Location -path $folderpath

     $projects = $folder.Projects

     if($projects.Count -gt 0)
     {
          foreach($project in $projects)
          {
               $projectpath = $folderpath + "\" + $project.Name + ".ispac"

               Write-Host "Exporting to $($projectpath) ...";
     
               [System.IO.File]::WriteAllBytes($projectpath, $project.GetProjectBytes())
          }
     }
 }
 <#
 .SYNOPSIS
 Exports all SSIS projects from an SSISDB database to a specified output directory
 .DESCRIPTION
 Retrieves all SSIS projects in .ispac format from an SSISDB database to a specified output directory, creating SSISDB folder structure.
 .PARAMETER Instance
 Specifies the SQL instance name
 .PARAMETER OutputDir
 Specifies the full output directory for SSISDB Folders/Projects to be exported to.
 .OUTPUTS
 No direct outputs from fuction - writes .ispac files.
 .EXAMPLE
 PS> Export-SSISProjectSSISDB -Instance SQLSSIS2014 -OutputDir "D:\DBA\SSIS\Export"
 Exports all SSIS projects from instance SQLSSIS2014 as .ispac files to SSISDB Folder-named subfolders created in D:\DBA\SSIS\Export 
 #>
 }