Use of Select-Object to change a property's data type and to add calculated properties

Question

Trying to get details from object and want to save it to SQL table.

$GetFiles `
    | Select-Object -Property  Name, Type `
    | Write-SqlTableData -ServerInstance $SQLServer -DatabaseName $SQLDatabase -SchemaName $SQLSchema -TableName $SQLTable -Credential $SQLCredential -Force

Que.1. Above code fails with error. Removing Type column works. But any way to cast Type property to string?

Write-SqlTableData : A mapping between .Net type 'Microsoft.Azure.Commands.DataLakeStore.Models.DataLakeStoreEnums+FileType' and SQL type for column 'Type' was not found. Consider removing the column with that type and repeat the operation.

Que.2. I also want to add an extra column say RowInsertedDate which will have current timestamp and one more hardcoded column say LoadStatus having value "Started". How to add it select clause?

Que.3. Anyway to truncate this table and then write data to it?

mklement0 · Accepted Answer · 2020-03-25T20:51:31.443

You need to use calculated properties with Select-Object, as described in this answer:

Re 1, converting the Type property values to strings ([string]):

$GetFiles |
  Select-Object -Property Name, @{ Name='Type'; Expression = { [string] $_.Type } }

Re 2, adding a RowInsertedDate column with a timestamp, and a LoadStatus column with fixed value 'Started':

$timestamp = Get-Date
$GetFiles |
  Select-Object Name, 
                @{ Name='Type'; Expression = { $_.Type.ToString() } },
                @{ Name='RowInsertedDate'; Expression = { $timestamp } },
                @{ Name='LoadStatus'; Expression = { 'Started' } }

^{Note: Positional arguments implicitly bind to the -Property parameter.}

Re 3, truncating the target table first:

I can't personally verify this, but I presume you need to use the Invoke-SqlCmd cmdlet with the
U-SQL TRUNCATE TABLE statement (since you're using Azure Data Lake).

Something along the following lines - obviously, use caution, because truncation is irreversible:

# UNTESTED. Note that TRUNCATE is IRREVERSIBLE.
Invoke-SqlCmd -Query "TRUNCATE TABLE $SQLSchema.$SQLTable" `
  -ServerInstance $SQLServer -Database $SQLDatabase -Credential $SQLCredential -Force

Use of Select-Object to change a property's data type and to add calculated properties

1 Answers1