Writing REST API result to Azure SQL

PowerShell Help
1

Hi all

I’ve been googling for hours now - but haven’t found a solution yet. So I’m trying this forum.

I’m getting some Power BI usage stats using:

$data = Get-PowerBIActivityEvent -StartDateTime ($startstring + 'T00:00:00.000')
-EndDateTime ($startstring + ‘T23:59:59.999’) `
-ActivityType ViewReport | ConvertFrom-Json

The result looks like this:

image
image714×936 137 KB

Next step is to write the rows to SQL server. Last week it worked with this script:
# 2. Select relevant columns
$activity = $data | Select Id, RecordType,CreationTime,Operation,OrganizationId,UserType,UserKey,Workload,UserId,ClientIP,UserAgent,Activity,ItemName,WorkSpaceName,DatasetName,ReportName ,CapacityId,CapacityName,WorkspaceId,AppName,ObjectId,DatasetId,ReportId,ArtifactId,ArtifactName,IsSuccess,ReportType,RequestId,ActivityId,AppReportId,DistributionMethod
,ConsumptionMethod,AppId,ArtifactKind

    # 3. Insert into SQL Server Database
        Write-SqlTableData -AccessToken $token -InputData $activity -ServerInstance $TargetServer -DatabaseName $TargetDb -SchemaName "sourceManual" -TableName "PowerBIReportViews" # -Force

But now I get the error:
Write-SqlTableData : The given value ‘System.Object’ of type Object from the data source cannot be converted to type nvarchar for Column 0 [Id] Row 1.
At line:7 char:13

  •         Write-SqlTableData -AccessToken $token -InputData $activi ...

  •         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    • CategoryInfo : WriteError: ([sourceManual].[PowerBIReportViews]:Table) [Write-SqlTableData], InvalidOperationException
    • FullyQualifiedErrorId : WriteToTableFailure,Microsoft.SqlServer.Management.PowerShell.WriteSqlTableData

I’m not an expert in PowerShell Objects so I need some help… :slight_smile:

/Rasmus