Tag: Automation

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 
 #>
 }

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\”

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
 #>
 }