Copy-DbaSsisCatalog error in Invoke-ProjectDeployment

mwilliamson1975 commented 6 months ago

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

I am receiving this when running against multiple source/destination servers ranging in version from SQL 2014 through SQL 2022 Developer and Enterprise editions:

Copy-DbaSsisCatalog -Source 'MySource01' -Destination 'MyDestination01' -Folder 'MyFolder01' -Project 'MyProject01' -Verbose
VERBOSE: [11:58:26][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the serve
r object
VERBOSE: [11:58:26][Connect-DbaInstance] authentication method is 'local integrated'
VERBOSE: [11:58:28][Connect-DbaInstance] String is passed in, will build server object from instance object and other parameters, do some checks and then return the serve
r object
VERBOSE: [11:58:28][Connect-DbaInstance] authentication method is 'local integrated'
VERBOSE: [11:58:37][Resolve-DbaNetworkName] Resolving MyDestination01 using .NET.Dns GetHostEntry
VERBOSE: [11:58:37][Resolve-DbaNetworkName] Resolving 192.168.1.100 using .NET.Dns GetHostByAddress
VERBOSE: [11:58:37][Get-DbaCmObject] Configuration loaded | Cache disabled: False
VERBOSE: [11:58:37][Get-DbaCmObject] [MyDestination01] Retrieving Management Information
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01] Accessing computer using Cim over DCOM
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01] Accessing computer using Cim over DCOM - Success
VERBOSE: [11:58:38][Resolve-DbaNetworkName] Resolving MyDestination01.mydomain.com using .NET.Dns GetHostEntry
VERBOSE: [11:58:38][Get-DbaCmObject] Configuration loaded | Cache disabled: False
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Retrieving Management Information
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Accessing computer using Cim over DCOM
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Accessing computer using Cim over DCOM - Success
VERBOSE: [11:58:38][Get-DbaService] Getting SQL Server namespaces on MyDestination01
VERBOSE: [11:58:38][Get-DbaCmObject] Configuration loaded | Cache disabled: False
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Retrieving Management Information
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Accessing computer using Cim over DCOM
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Accessing computer using Cim over DCOM - Success
VERBOSE: [11:58:38][Get-DbaService] The following namespaces have been found: ComputerManagement16.
VERBOSE: [11:58:38][Get-DbaService] Getting Cim class SqlService in Namespace ComputerManagement16 on MyDestination01.
VERBOSE: [11:58:38][Get-DbaCmObject] Configuration loaded | Cache disabled: False
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Retrieving Management Information
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Accessing computer using Cim over DCOM
VERBOSE: [11:58:38][Get-DbaCmObject] [MyDestination01.mydomain.com] Accessing computer using Cim over DCOM - Success
VERBOSE: [11:58:38][Get-DbaService] Found service MsDtsServer160 in namespace ComputerManagement16.
VERBOSE: [11:58:38][Get-RemoteIntegrationService] Service SQL Server Integration Services 16.0 was found running on the destination.
VERBOSE: Performing the operation "Creating folder MyFolder01" on target "MyDestination01".
VERBOSE: [11:58:39][New-CatalogFolder] Creating folder MyFolder01.
VERBOSE: Performing the operation "Refresh folders for project deployment" on target "MyDestination01".
VERBOSE: Performing the operation "Deploying project MyProject01 from folder MyFolder01" on target "MyDestination01".
VERBOSE: [11:58:43][Invoke-ProjectDeployment] Deploying project MyProject01 from folder MyFolder01.
WARNING: [11:58:43][Invoke-ProjectDeployment] Failed to deploy project. | Unable to match the identifier name MyFolder01 to a valid enumerator name. Specify one of the follow
ing enumerator names and try again:
BigInt, Binary, Bit, Char, DateTime, Decimal, Float, Image, Int, Money, NChar, NText, NVarChar, Real, UniqueIdentifier, SmallDateTime, SmallInt, SmallMoney, Text, Timesta
mp, TinyInt, VarBinary, VarChar, Variant, Xml, Udt, Structured, Date, Time, DateTime2, DateTimeOffset
VERBOSE: Performing the operation "Deploying environment Environment01 from folder MyFolder01" on target "MyDestination01".
VERBOSE: [11:58:44][New-FolderEnvironment] Creating environment Environment01.

Steps to Reproduce

I have tried:

Copy-DbaSsisCatalog -Source 'MySource01' -Destination 'MyDestination01' -Folder 'MyFolder01' 

Copy-DbaSsisCatalog -Source 'MySource01' -Destination 'MyDestination01' -Folder 'MyFolder01' -Project 'MyProject01'

Copy-DbaSsisCatalog -Source 'MySource01' -Destination 'MyDestination01' -Project 'MyProject01'

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

2.1.6

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

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

SQL Server Edition and Build number

In the verbose output from above, I used:

Source: Microsoft SQL Server 2022 (RTM-CU7) (KB5028743) - 16.0.4065.3 (X64)   Jul 25 2023 18:03:43   Copyright (C) 2022 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) 

Destination: Microsoft SQL Server 2022 (RTM-CU9) (KB5030731) - 16.0.4085.2 (X64)   Sep 27 2023 12:05:43   Copyright (C) 2022 Microsoft Corporation  Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

.NET Framework Version

.NET Framework 4.8.4645.0

mwilliamson1975 commented 6 months ago

I did a little more digging after initially opening this ticket. It appears that the command fails when setting the first parameter value in the Invoke-PorjectDeployment function. The error reported is:

"Cannot convert argument "sqlDbType", with value: "MyFolder01", for "Add" to type "System.Data.SqlDbType": "Cannot convert value "MyFolder01" to type "System.Data.SqlDbType"

This occurs on line 155 and I believe that it expects a data type instead of the folder name, then the parameter can be subsequently set. Instead of:

$cmd.Parameters.Add("@folder_name", $Folder) | Out-Null

it should be:

$cmd.Parameters.Add("@folder_name", [Data.SQLDBType]::NVarChar, 128).Value = $Folder

or:

$cmd.Parameters.AddWithValue("@folder_name", $Folder);

Likewise for the following line using the project variable.

If I can find the time to fork and test this out, then I'll submit a PR.

mwilliamson1975 commented 6 months ago

Updating the parameter strings fixed the initial issue. There is a subsequent error that arises in from Invoke-ProjectDeployment on line 162:

Method not found: 'System.Data.SqlClient.SqlConnection Microsoft.SqlServer.Management.Common.ConnectionManager.get_SqlConnectionObject()'

I receive this same error on multiple machines, and it occurs when running the canned scripts that Microsoft has supplied as examples for DeployProject as well (https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-deploy-powershell?view=sql-server-ver16).

dataplat / dbatools