Copy-DbaSsisCatalog can't connect to SSIS services

tboggiano commented 1 year ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

What if: Performing the operation "Making a new Connection String" on target "server2014". What if: Performing the operation "Making a new Connection String" on target "server2014". WARNING: [14:59:23][Copy-DbaSsisCatalog] There was an error connecting to the source integration services. | The ConnectionString property has not been initialized. What if: Performing the operation "Set variable" on target "Name: __dbatools_interrupt_function_78Q9VPrM6999g6zo24Qn83m09XF56InEn4hFrA8Fwhu5xJrs6r Value: True". What if: Performing the operation "Making a new Connection String" on target "server2022". What if: Performing the operation "Making a new Connection String" on target "server2022". WARNING: [14:59:28][Copy-DbaSsisCatalog] There was an error connecting to the destination integration services. | The ConnectionString property has not been initialized. What if: Performing the operation "Set variable" on target "Name: __dbatools_interrupt_function_78Q9VPrM6999g6zo24Qn83m09XF56InEn4hFrA8Fwhu5xJrs6r Value: True". What if: Performing the operation "Create destination SSISDB Catalog" on target "server2022". The destination SSISDB catalog does not exist. At line:15577 char:21

... throw "The destination SSISDB catalog does not exist."


+ CategoryInfo          : OperationStopped: (The destination...does not exist.:String) [], RuntimeException
+ FullyQualifiedErrorId : The destination SSISDB catalog does not exist.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference
Copy-DbaSsisCatalog -Source server2017 -Destination server2022 -WhatIf -Verbose

Please confirm that you are running the most recent version of dbatools

2.0.4

Other details or mentions

Trying to do this for upgrades to 2022. It fails to connect to the catalogs on the source and destination. I've used the copy commands for everything else and don't know what is going on with this one. I've tried this in PS 7 Core and PS 5.1 no dice, the same errors. I noticed in PS Core it tells you can't run this if you are in a MAC or Linux, that probably should be fixed to an accurate message of some sort.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), Windows PowerShell (powershell.exe), VS Code (terminal)

PowerShell Host Version

Name Value

PSVersion 7.3.5 PSEdition Core GitCommitId 7.3.5 OS Microsoft Windows 10.0.19044 Platform Win32NT PSCompatibleVersions {1.0, 2.0, 3.0, 4.0…} PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1 WSManStackVersion 3.0

SQL Server Edition and Build number

Microsoft SQL Server 2017 (RTM-CU31-GDR) (KB5021126) - 14.0.3460.9 (X64) Jan 25 2023 08:42:43 Copyright (C) 2017 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Microsoft SQL Server 2019 (RTM-CU20) (KB5024276) - 15.0.4312.2 (X64) Apr 1 2023 12:10:46 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

Microsoft SQL Server 2022 (RTM-CU5) (KB5026806) - 16.0.4045.3 (X64) May 26 2023 12:52:08 Copyright (C) 2022 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022 Datacenter 10.0 (Build 20348: ) (Hypervisor)

.NET Framework Version

.NET 7.0.8

andreasjordan commented 1 year ago

Maybe fixed by https://github.com/dataplat/dbatools/pull/9005/files - please test again with the development branch.

tboggiano commented 1 year ago

I get slightly different errors with that development branch, most notable is not being able to find the assembly type.

WARNING: [14:59:30][Copy-DbaSsisCatalog] This command is not supported on Linux or macOS What if: Performing the operation "Set variable" on target "Name: __dbatools_interrupt_function_78Q9VPrM6999g6zo24Qn83m09XF56InEn4hFrA8Fwhu5xJrs6r Value: True". VERBOSE: [14:59:30][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the server object VERBOSE: [14:59:30][Connect-DbaInstance] authentication method is 'local integrated' WARNING: [14:59:31][Copy-DbaSsisCatalog] An error occurred when checking the destination for Integration Services. Is Integration Services installed? | The term 'Get-RemoteIntegrationService' is not recognized as a name of a cmdlet, function, script file, or executable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
What if: Performing the operation "Set variable" on target "Name: __dbatools_interrupt_function_78Q9VPrM6999g6zo24Qn83m09XF56InEn4hFrA8Fwhu5xJrs6r Value: True".

WARNING: [14:59:31][Copy-DbaSsisCatalog] There was an error connecting to the destination integration services. | Cannot find type [Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices]: verify that the assembly containing this type is loaded.

What if: Performing the operation "Set variable" on target "Name: __dbatools_interrupt_function_78Q9VPrM6999g6zo24Qn83m09XF56InEn4hFrA8Fwhu5xJrs6r Value: True". VERBOSE: [14:59:31][Copy-DbaSsisCatalog] SQL CLR configuration option is already enabled at the destination. What if: Performing the operation "Create destination SSISDB Catalog" on target "server2022". Exception: C:\Users\admtboggiano\Documents\GitHub\dbatools\public\Copy-DbaSsisCatalog.ps1:380:21 Line | 380 | … throw "The destination SSISDB catalog does not exist." | ~~~~~~~~~~~~~~ | The destination SSISDB catalog does not exist.

andreasjordan commented 1 year ago

Thanks for the feedback. My problem is that I don't work SSIS and would have to set up a better lab first. Will try to find time...

dataplat / dbatools