dataplat
Invoke-DbaDbDataMasking.ps1 failing on bit columns
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
WARNING: [12:44:01][Invoke-DbaDbDataMasking] Could not convert value | A positional parameter cannot be found that accepts argument 'Could not convert the value. Value ' ' is not valid BIT or BOOL'.
Followed by...
UPDATE [dbo].[myTable] SET [bitfield] = WHERE [TrackingRowId] = 447503492;
... notice that bitfield is equal to nothing. I also ran a trace to confirm this is what is being executed on the server.
Steps to Reproduce
CREATE TABLE DJBoolTest (bitfield bit)
INSERT INTO DJBoolTest (bitfield) VALUES (0)
New-DbaDbMaskingConfig -SqlInstance server -Database database -Table table -Path C:\Temp\clone2
Invoke-DbaDbDataMasking -Column bitfield -Confirm:$false -SqlInstance server -Database database -Table table -FilePath C:\Temp\clone2\file.json
Please confirm that you are running the most recent version of dbatools
Yes, I updated to 2.1.5. Issue also happens 1.1.114
Other details or mentions
Example json config having issues:
{ "Name": "database", "Type": "DataMaskingConfiguration", "Tables": [ { "Name": "table", "Schema": "schema", "Columns": [ { "Name": "bitfield", "ColumnType": "bit", "CharacterString": null, "MinValue": 0, "MaxValue": 1, "MaskingType": "Random", "SubType": "Bool", "Format": null, "Separator": null, "Deterministic": false, "Nullable": true, "KeepNull": true, "Composite": null, "Action": null, "StaticValue": null } ] } ] }
What PowerShell host was used when producing this error
PowerShell Core (pwsh.exe)
PowerShell Host Version
7.4
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 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
.NET Framework Version
.NET 8.0.0
Looks like this issue also occurs for integer/decimal format:
WARNING: [13:54:09][Invoke-DbaDbDataMasking] Could not convert value | A positional parameter cannot be found that accepts argument 'Could not convert the value. Value ' ' is not valid integer/decimal format'.
The data masking commands are a bit stale and I don't have a lab to test them. If you could have a look at the source code and find the bug, I may be able to provide a fix.
Maybe @sanderstad has time to have a look...
