locked
Select-object related RRS feed

  • 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? 


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Tuesday, March 24, 2020 6:50 PM

Answers

All replies

  • "Type" is not an Azure type.

    Proper formatting would make you code easier to write, more reliable and avoid hard to find errors.

    $param = @{
        ServerInstance = $SQLServer
        DatabaseName = $SQLDatabase
        SchemaName = $SQLSchema
        TableName = $SQLTable
        Credential = $SQLCredential
        Force = $true
    }
    
    $GetFiles |
        Select-Object -Property  Name, Type |
        Write-SqlTableData @param

    "Type" is not a valid property of a file and is not a valid Azure type.

    For detailed questions about how to use SQL and Azure Datalake schemas please post in the correct Azure forum.


    \_(ツ)_/


    • Edited by jrv Tuesday, March 24, 2020 8:52 PM
    Tuesday, March 24, 2020 8:50 PM

  • "Type" is not a valid property of a file and is not a valid Azure type.

    For detailed questions about how to use SQL and Azure Datalake schemas please post in the correct Azure forum.

    I am taking all files from Azure data lake using the command given in below. Please open output section in bottom and then you will Type property. Yes it has some different data type (not string, Int etc).

    https://docs.microsoft.com/en-us/powershell/module/az.datalakestore/get-azdatalakestorechilditem?view=azps-3.6.1

    So is it not possible to cast property data types here? 

    Let's say, something like this - 

    $GetFiles | Select-Object -Property Name, Type.ToString() ? 

    And adding a column is possible?  like

    $GetFiles | Select-Object -Property Name, "RowInsertedDate" map to Getdate() ? 


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, March 25, 2020 7:32 AM
  • I fit was "cast-able" it wouldn't have caused an error.  

    What datatype is the column?  "FileType is an enum".  It my only be converted into a azure type which may be something different from the database type.

    I suggest posting in an Azyre forum for DataLake to find out how to work with these things.


    \_(ツ)_/

    Wednesday, March 25, 2020 8:07 AM
  • Got the detailed answer here - https://stackoverflow.com/questions/60848871/use-of-select-object-to-change-a-propertys-data-type-and-to-add-calculated-prop

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, April 24, 2020 1:02 PM
  • I fit was "cast-able" it wouldn't have caused an error.  

    What datatype is the column?  "FileType is an enum".  It my only be converted into a azure type which may be something different from the database type.

    I suggest posting in an Azyre forum for DataLake to find out how to work with these things.


    \_(ツ)_/

    Thanks jrv. Marking your posts as helpful. 

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Friday, April 24, 2020 1:03 PM