Missing rows when inserting data into SQL via PowerShell

Question

I have a script that pulls data from a external source (five9) every 5 minutes and loads it into a SQL server, shown below. The script seems to work for the most part, but whenever I compare the results for the prior day to the results from the external source, I'm always a few hundred to a thousand rows short in SQL (out of 50,000 to 100,000 total rows). The scripts run without any errors in PowerShell.

I've got a second identical script with different start/end times to pull the last 24 hours running every night, and while that did reduce the missing rows a little the issues has persisted.

Duplicate values are restricted by a unique key value that is concatenated from other values in each row. I have tried turning off the is unique setting in SQL and deduping manually but still had missing rows.

I have verified that my unique string actually is unique by dumping the results from the external source into an excel file and concatenating there, then checking for duplicates. This does not turn up any results.

I have been able to identify which rows precisely are missing from SQL, and unfortunately nothing about that data is really jumping out at me as problematic.

Any help or insight anyone could offer would be greatly appreciated. Been pulling my hair out over this for over a month now lol.

$reportid = Start-Five9Report -FolderName folder -ReportName report -EndDateTime (Get-Date).AddMinutes(-1) -StartDateTime (Get-Date).AddMinutes(-6)
$reportid
$reportresult = Get-Five9ReportResult -Identifier $reportid

$timeZoneOffset = 

@{
    PST = 0
    MST = 1
    CST = 2
    EST = 3
}


$localTimeZone = 'EST'

foreach($reportresult in $reportresult)

{

$VARDATE = $reportresult.Date
$VARTIME =  $reportresult.TIME
$VARDATETIME = "$($VARDATE)T$VARTIME"
$REPORTDATETIME = ([datetime]$VARDATETIME).AddHours($timeZoneOffset[$localTimeZone]).ToString('s')

$DATE = $REPORTDATETIME.Substring(0,10)
$HOUR = "$($REPORTDATETIME.Substring(11,3))00:00"
$MONTH = $REPORTDATETIME.Substring(5,2)
$TIME = $REPORTDATETIME.Substring(11,8)
$YEAR = $REPORTDATETIME.Substring(0,4)
${REPORT RESULTS} = $reportresult.("REPORT RESULTs")



$insertquery="

Insert into [dbo].[table]

(

[VALUE],
//many other values

)

values

(

'$($DATE -replace "'", "''")',
'$($HOUR -replace "'", "''")',
'$($MONTH -replace "'", "''")',
'$($TIME -replace "'", "''")',
'$($YEAR -replace "'", "''")',
'$($VALUE -replace "'", "''")',
'$(${SPACED VALUE} -replace "'", "''")',
//many other values
'$("${VALUE}${VALUE}${VALUE}${VALUE}${VALUE}${VALUE}${VALUE}${VALUE}")' //Combined from several fields to serve as unique key

)

GO

"
Invoke-Sqlcmd -ServerInstance 'server' -query $insertquery -Username user -Password ("password") -database database

};```

Possibly your string building to pass a query along to Invoke-SqlCmd is creating invalid query strings. Is there some reason you're not using [SqlCommand](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand) and its typed [Parameters](https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters) collection? If that's a step too large you could try logging the constructed queries to a file instead, and then try to correlate which ones are missing from the database. — AlwaysLearning, Oct 20 '21 at 13:37

score 0 · Answer 1 · answered Mar 28 '22 at 07:10

It's hard work to build an insert statement on the fly so it is possible some character or string truncation or something strange is happening.

If you're able to import a community module you could look at dbatools. They have a Write-DbaDbTableData which can take a PowerShell object and insert the data into a SQL Server table.

You could do something like this to build a PowerShell object and then insert the data into the table.

You could also then export the $results object to a csv or text file and then compare the results with SQL Server table rows to see if there are still issues.

$results = @()
foreach($r in $reportresult) {
    $results += [PSCustomObject]@{
        value = ('"{0} {1} {2}' -f $r.Date.Hour, $r.Date.Date, $r.Results)
    }
}

Write-DbaDbTableData -SqlInstance ServerName -Database pubs -InputObject $results -Table 'table'

Missing rows when inserting data into SQL via PowerShell

1 Answers1