SSIS deploy ispac from package fails when incompatable sqlserver powershell module is used

[bcullman]

Step template

ssis-deploy-ispac-from-package-parameter

Step version

6

Octopus version

2025.4.10250

Step template parameter inputs

Database server name (\instance) :#{DatabaseServer}
SQL Authentication Username: [EMPTY]
SQL Authentication Password: [EMPTY]
Enable SQL CLR: False
Catalog name: SSISDB
Catalog password: [EMPTY]
Folder name: [A VALID FOLDER NAME WAS USED HERE]
Use environment: True
Environment name: #{EnvironmentName}
Reference project parameters to environment variables: True
Reference package parameters to environment variables: True
Use Fully Qualified Variable Names: False
Use Custom Filter for connection manager properties: False
Custom Filter for connection manager properties: [EMPTY]
Clean obsolete variables from environment: False
Package Id: [A VALID PACKAGEID WAS USED HERE]

What happened

Got the following Octo output:

Extracting dependency 'C:\Octopus\Files\[REDACTED].nupkg' to 'C:\Octopus\Work\[REDACTED]\SSIS.Template.ssisPackageId' 
NotSpecified: Exception calling "LoadFile" with "1" argument(s): "The module was expected to contain an assembly manifest. (Exception from HRESULT: 0x80131018)" 
At C:\Octopus\Work\[REDACTED]\Script.ps1:69 char:9 
+         [Reflection.Assembly]::LoadFile($assemblyFile.FullName) | Out ... 
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
at Load-SqlServerAssmblies, C:\Octopus\Work\[REDACTED]\Script.ps1: line 69 
at <ScriptBlock>, C:\Octopus\Work\[REDACTED]\Script.ps1: line 482 
at <ScriptBlock>, <No file>: line 1 
at <ScriptBlock>, C:\Octopus\Work\[REDACTED]\Octopus.FunctionAppenderContext.ps1: line 276 
at <ScriptBlock>, C:\Octopus\Work\[REDACTED]\Bootstrap.Octopus.FunctionAppenderContext.ps1: line 6485 
at <ScriptBlock>, <No file>: line 1 
at <ScriptBlock>, <No file>: line 1 
The remote script failed with exit code 1 

Reproduction steps

Ran the Step against a server that uses SSIS, that already had a SqlServer PSModule v22.3.0 installed.

More Information

The underlying problem here is that for this Step Template to work, it needs to use a SqlServer PSModule that contains the needed assemples for SSIS. Unfortunately these were removed as of version 22.0.59 The author of this step template is aware of this, and has noted it here:

https://octopus.com/integrations/sql-server/deploy-ispac-ssis-project-from-a-package-parameter

As currently written, if this is Step Template is run on a machine that does not contain a SqlServer PSModule, it will install the pinned compatible version 21.1.18256

However, our organization has since moved on, and now has version of SqlServer PSModule higher than 21.1.18256. rather than recognizing that, the script tries to use it, and fails.

A PR will be submitted to resolve this issue