Can't write DBNull to SQL in Powershell

In Powershell, I have some data that I am importing from a .csv, and some data points aren't always entered so I need to replace them with [DBNull]::Value.

The problem is that when I go to use Write-SqlTableData I get this error:

Write-SqlTableData : Invalid storage type: DBNull.

I'm using this code to detect blank spaces and replace them with DBNull's:

$DataOut | Foreach-Object {
    ForEach ($Property in $_.PSObject.Properties) {
        If([String]::IsNullOrEmpty($Property.Value)) {
            $_.($Property.Name) = [DBNull]::Value
        }
    }
}

What is the issue here? What is an alternative?

I am having the exactly the same issue and I come to this web page. But it really did not solve the issue other than what Shawn Metlton mentioned to use write-dbadatatable in the comment.

Since the original poster @discipline did not give the whole PS script, I cannot fix his issue. But I figure out how to do the work after one hour's try.

So here is how to repeat the same error message

We first create a table in [TempDB] on my local sql server instance

use tempdb
if object_id('dbo.t', 'U') is not null
    drop table dbo.t;
create table dbo.t (name varchar(100), gender varchar(10), id int)

Now we write a few lines of PS codes as follows:

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city='Toronto'}
#this can be successful
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $p;

After execution, we can check the sql table and see it is indeed successful as shown below

Now if we change the $p to have its [city] property to DBNull as the following

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city=[dbnull]::Value}
#this will fail
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $p;

we can see the following error

The alternative solution is to create a system.data.datatable and then pass the object value to this datatable and then pass datatable to write-sqltabledata as shown below

$dt = new-object 'System.Data.DataTable';
$dt.Columns.Add((new-object System.Data.DataColumn('name', [System.String])));
$dt.Columns.Add((new-object System.Data.DataColumn('gender', [System.String])));
$dt.Columns.Add((new-object System.Data.DataColumn('city', [System.String])));

$p = [pscustomobject] @{name='jeffrey'; gender='male'; city=[dbnull]::Value}

$p | % {$r = $dt.NewRow(); $r.name=$_.name; $r.gender=$_.gender; $r.city=$_.city; $dt.rows.add($r);}
#this will succeed
Write-SqlTableData -ServerInstance localhost -DatabaseName tempdb -SchemaName dbo -TableName t -InputData $dt;

Now if we query the sql table again, we will see it works

If you have other better ideas, please share for the commuity.

StackExchange Database Administrators Q#181537, answer score: 2