Dbatools: [Bug] Copy-DbaSsisCatalog - Could not load file or assembly Microsoft.SqlServer.IntegrationServices.Common.ObjectModel, Version=15

[garethnewman]

PowerShell version:

Name Value
---- -----
PSVersion 5.1.14409.1018
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.14409.1018
CLRVersion 4.0.30319.42000
WSManStackVersion 3.0
PSRemotingProtocolVersion 2.3
SerializationVersion 1.1.0.1

dbatools Module version:

Name : dbatools
Path : C:\Program Files\WindowsPowerShell\Modules\dbatools\1.0.54\dbatools.psd1
Version : 1.0.54

Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) Mar 18 2018 09:11:49 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 8.1 Pro 6.3 (Build 9600: )

Report

Using Copy-DbaSsisCatalog -Folder seems to look for version 15 of SSIS instead of grabbing any available version.

Host used

• [X] powershell.exe
• [ ] ISE
• [X] VS Code
• [ ] Other (please specify)

If anything other than powershell.exe was used, please confirm that you can duplicate the issue with powershell.exe

• [X] Still buggy in powershell.exe (exactly the same)

Errors Received

writeErrorStream : True
PSMessageDetails :
Exception : System.Exception: Could not load file or assembly
'Microsoft.SqlServer.IntegrationServices.Common.ObjectModel, Version=15.100.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system
cannot find the file specified. ---> System.Management.Automation.MethodInvocationException:
Exception calling "Create" with "0" argument(s): "Could not load file or assembly
'Microsoft.SqlServer.IntegrationServices.Common.ObjectModel, Version=15.100.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies.

Steps to Reproduce

Try and use Copy-DbaSsisCatalog with the folder option. Don't have version 15 of Microsoft.SqlServer.IntegrationServices.Common.ObjectModel available (have an older version like 13.0 or 14.0, in my case 2016)

Copy-DbaSsisCatalog -Source $source -Destination $dest -Folder $folder

Expected Behavior

expected $folder` to appear on $dest.

Actual Behavior

Error: Could not load file or assembly

[potatoqualitee]

Thank you for the report. This command is problematic and I don't know how to fix it. it's likely that i'll remove the command soon unless someone who knows about DLLs can fix the issue.

[garethnewman]

Yeah, I am not sure either. I tried LoadWithPartialName (deprecated) to grab the available dll and that got me a bit further but then it still fails trying to grab the latest one. :(

[potatoqualitee]

what we coullllllllllld do is

• Remove DLL and references
• Require person to have SSIS installed locally
• Use code in Get-DbaManagementObject to help determine the latest version, and load that
• If that does not load, fail gracefully

[sqlegan]

I've been battling with this on and off for a week or so. What seemed to be the case was that the assembly MS.SQL.IS.Common.ObjectModel.dll is not included in the smo folder (mine only had two SSIS assemblies)
So I went into the SSMS install directory and copied all three of the assemblies (so they were the same version) into the smo directory and suddenly the command started working.

[sqlegan]

Also having SSIS installed locally did not help, I think the assemblies you need are shipped with ssms

[ccpDeNormalized]

Ran into this issue today... it appears to be the only command that fails so far in my sql 2017 to sql 2019 migration.

[ChristianFalk]

Encountered the same issue trying to synchronize SSIS catalog between two SQL Server 2019 instances. Adding / replacing Microsoft.SqlServer.IntegrationServices.Common.ObjectModel.dll, Microsoft.SqlServer.Management.IntegrationServices.dll and Microsoft.SqlServer.Management.IntegrationServicesEnum.dll in folder dbatools\bin\smo, copied from an SSMS 18.4 installation resolved the issue.

[PeteForster]

Christians solution worked for me today too, the dlls from 18.3 made no difference but downloading 18.4 and using those dlls did the trick.