Automating SSISDB Migration 1/4 – Exporting MSDB Packages with Powershell

I was recently faced with the task of a fairly massive migration of SSIS packages to SQL 2017, mostly from 2012/2014 but including a few 2008. Cool! 2017 is new, not ‘shiny shiny new’ but ‘still quite shiny, not so many catastrophic early-life bugs’ new, and that’s good. But we decided against the ‘SSISDB upgrade’ feature present in SQL2016+ for reasons, so, ouch, that’s a lot of work. Obviously, I immediately did what any lazy sysadmin worth their salt would do – try to automate the crap out of it.

A few Powershell scripts later and mission mostly complete. So I’ll share them for the handful loads of DBAs potentially having to do this kind of job because their SSIS servers were running critical, bespoke software locked to the server on an ‘if it ain’t broke don’t fix it’ adage from above.

The first one is for exporting packages from MSDB. I feel dirty saying ‘package’ and ‘MSDB’ in the same sentence. Hopefully this blog post is the last time I ever do.

The script below is a function that takes the instance name and either an output directory or DisplayOnly switch. Using the DisplayOnly switch means it just returns a list of folders and package names. Specifying an output directory exports all packages on the instance as .dtsx files by pulling the binary package data from the relevant MSDB tables.

Github link

Run the function to load into cache then call it thus;

Export-SSISPackageMSDB -Instance OldSQLInstance -OutputDir “C:\ancient\old\stuff\”

Export-SSISPackageMSDB
function Export-SSISPackageMSDB
 {
 param
 (
     [Parameter(position=0, mandatory=$true)][string]$Instance,
     [Parameter(position=1, mandatory=$false)][string]$OutputDir,
     [Parameter(position=2, mandatory=$false)][switch]$DisplayOnly
 )
 Import-Module SqlServer
 # Exit if OutputDir not specified without DisplayOnly switch
 if(!$DisplayOnly)
 {
     if(!$OutputDir)
     {
         Write-Output "Error - must specify OutputDir unless using DisplayOnly switch"
     return } if(!(Test-Path $OutputDir)) {     Write-Output "Error - invalid path specified in OutputDir"     return }
 }
 # Sanitise path input
 if(!$OutputDir.EndsWith('\'))
 {
     $OutputDir = $OutputDir + '\'
 }
 $versionquery = "SELECT CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(4000)),CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(4000))) - 1) AS DECIMAL(9,1)) AS 'Version'"
 # Get SQL version, exit if cannot connect to instance to perform this query
 try
 {
     $version = (Invoke-Sqlcmd -ServerInstance $Instance -Query $versionquery -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop).Version
 }
 catch
 {
     Write-Output "Error connecting to server."
     Write-Output $error[0]
 return
 }
 # Set SSIS table names dependent on version
 if ($version -gt 9)
 {
     Write-Debug "Version -gt 9"
     $SSISFoldersTable = 'sysssispackagefolders'
     $SSISPackageTable = 'sysssispackages'
 }
 else
 {
     Write-Debug "Version -le 9"
     $SSISFoldersTable = 'sysdtspackagefolders90'
     $SSISPackageTable = 'sysdtspackages90'
 }
 $PackagesQuery = "WITH cte AS
                     (
                         SELECT CAST(foldername AS VARCHAR(MAX)) AS 'FolderPath', folderid
                         FROM msdb.dbo.$($SSISFoldersTable)
                         WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
                         UNION ALL
                         SELECT CAST(c.folderpath + '\' + f.foldername AS VARCHAR(MAX)), f.folderid
                         FROM msdb.dbo.$($SSISFoldersTable) f
                         INNER JOIN cte c 
                             ON c.folderid = f.parentfolderid
                     )
                     SELECT c.FolderPath, p.name, CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as 'pkg'
                     FROM cte c
                     INNER JOIN msdb.dbo.$SSISPackageTable p 
                         ON c.folderid = p.folderid
                     WHERE c.FolderPath NOT LIKE 'Data Collector%'
                     UNION
                     SELECT NULL, p.name, CAST(CAST(p.packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS 'pkg'
                     FROM msdb.dbo.$($SSISFoldersTable) f
                     INNER JOIN msdb.dbo.$SSISPackageTable p
                         ON f.folderid = p.folderid
                     WHERE f.folderid = '00000000-0000-0000-0000-000000000000'"
 $PackagesQueryDisplayOnly = "WITH cte AS
                             (
                                 SELECT CAST(foldername AS VARCHAR(MAX)) AS 'FolderPath', folderid
                                 FROM msdb.dbo.$($SSISFoldersTable)
                                 WHERE parentfolderid = '00000000-0000-0000-0000-000000000000'
                                 UNION ALL
                                 SELECT CAST(c.FolderPath + '\' + f.foldername AS VARCHAR(MAX)), f.folderid
                                 FROM msdb.dbo.$($SSISFoldersTable) f
                                 INNER JOIN cte c 
                                     ON c.folderid = f.parentfolderid
                             )
                             SELECT c.FolderPath, p.name
                             FROM cte c
                             INNER JOIN msdb.dbo.$SSISPackageTable p 
                                 ON c.folderid = p.folderid
                             WHERE c.FolderPath NOT LIKE 'Data Collector%'
                             UNION
                             SELECT NULL, p.name
                             FROM msdb.dbo.$($SSISFoldersTable) f
                             INNER JOIN msdb.dbo.$SSISPackageTable p
                                 ON f.folderid = p.folderid
                             WHERE f.folderid = '00000000-0000-0000-0000-000000000000'"
 Write-Output "SSIS Packages being retrieved;"
 if($DisplayOnly)
 {
     try
     {
         $packages = Invoke-Sqlcmd -ServerInstance $Instance -Database msdb -Query $PackagesQueryDisplayOnly -QueryTimeout 10 -ConnectionTimeout 10 -ErrorAction Stop
     $i = 0     foreach($package in $packages)     {         Write-Host $package.Folderpath "/" $package.Name         $i++     }     Write-Output "Total $($i) packages." } catch {     Write-Output "Error retrieving packages."     Write-Output $error[0]     return }
 }
 else
 {
     try
     {
         $packages = Invoke-Sqlcmd -ServerInstance $Instance -Database msdb -Query $PackagesQuery -MaxBinaryLength 100000 -MaxCharLength 10000000
     try     {         $i = 0         foreach($package in $packages)         {             $package.pkg | Out-File -Force -Encoding ASCII -FilePath ("" + $($OutputDir) + $($package.Name) + ".dtsx")             $i++         }         Write-Output "$($i) packages successfully written to $($OutputDir)."     }     catch     {         Write-Output "Error writing .dtsx files to specified location;"         Write-Output $error[0]         return     } } catch {     Write-Output "Error retrieving packages from MSDB;"     Write-Output $error[0]     return }
 }
 <#
 .SYNOPSIS
 Retrieves all SSIS packages from MSDB database.
 .DESCRIPTION
 Retrieves all SSIS packages from MSDB database, optionally saving the .dtsx package files to
 a designated output.
 .PARAMETER Instance
 Specifies the SQL instance name
 .PARAMETER OutputDir
 Specifies the full output directory for SSIS packages to be exported to.
 .PARAMETER DisplayOnly
 Switch parameter that causes function to just output a list of SSIS package folders and names.
 .OUTPUTS
 No direct outputs from fuction - returns list of SSIS packages or writes .dtsx files.
 .EXAMPLE
 PS> Export-SSISPackageMSDB -Instance MYSQL2008SERVER -OutputDir "C:\DBA\SSIS\Export\"
 Exports all SSIS packages from MYSQL2008SERVER as .dtsx files to C:\DBA\SSIS\Export\ 
 .EXAMPLE
 PS> Export-SSISPackageMSDB -Instance MYSQL2008SERVER -DisplayOnly
 Displays list of SSIS packages on MYSQL2008SERVER
 #>
 }