Error loading Microsoft.SqlServer.Management.IntegrationServices assembly registered in the GAC with powershell 7

Question

I'm trying to convert a PowerShell script from PowerShell 5 (Windows PowerShell) to run it with PowerShell 7 (PowerShell core)

The purpose of this script is to install IntegrationServices projects to the specified SQL Server instance.

The problem happens when trying to import the assembly from the Global Assembly Cache (GAC)

This line is working with PowerShell 5, but raises an error when executed with PowerShell 7

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

This raises the following error

MethodInvocationException: E:\Work\temp\load-assembly.ps1:6
Line |
   6 |   [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Mana .
     |   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Exception calling "LoadWithPartialName" with "1" argument(s): "Could not load file or assembly
     | 'Microsoft.SqlServer.Management.IntegrationServices, Culture=neutral, PublicKeyToken=null'. Operation is not supported.
     | (0x80131515)"

I didn't find an explanation if this error is because this Assembly is not compatible with .Net Core or if a different syntax is needed to load this assembly with Powershell core.

I've also tried to use the Load method by specifying the full name of the assembly, but I get the same result as when using the LoadWithPartialName method.

$loadStatus = [System.Reflection.Assembly]::Load("Microsoft.SqlServer.Management.IntegrationServices, "+
           "Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL")

Answer 1

Open a Powershell 7 terminal as administrator and run the following orders :

$rootpath = "C:\Program Files\PackageManagement\NuGet\Packages\"
mkdir $rootpath -Force
cd $rootpath

$sourceNugetExe = "https://dist.nuget.org/win-x86-commandline/latest/nuget.exe"
$targetNugetExe = "$rootPath\nuget.exe"
Invoke-WebRequest $sourceNugetExe -OutFile $targetNugetExe
./nuget install Microsoft.SqlServer.SqlManagementObjects # -version 150.18208.0     
./nuget install Microsoft.Data.SqlClient # -version 1.1.1

Install-Module -Name SqlServer -AllowClobber -Force

After that in your code simply use

Import-Module SqlServer