I wrote a script that runs queries on a SQL Server DB (2019) and writes the results to Excel files using the Export-Excel function of the PS module ImportExcel. It works great for mid-sized datasets, but it is very slow for larger ones. Also, for sufficiently large datasets, memory availability becomes a problem. I suspect both of these issues can be attributed to having to first load data into a System.Data.DataTable, so, in an attempt to circumvent this, I modified Export-Excel to accept a System.Data.IDataReader. This was a huge performance improvement, and it works perfectly when I run the script on my machine. However, when I attempt to invoke the script from within a SQL job, it just fills the a file’s first column with the value “System.Data.Common.DataRecordInternal” for every row in the dataset. Maybe this has something to do with differences in assembly versions on my machine and the server. Note that both System.Data.DataTable and System.Data.IDataReader are in the same assembly. The SQL job has a PowerShell step that looks like the following:
#NOSQLPS
PowerShell.exe -File "folderPathfileName.ps1"
I have spent quite a bit of time trying to figure this out, and I just don’t know what I am missing. Any ideas or suggestions would be helpful.
Here is the link to my version of the function. I would include whole function in this post, but it would end up exceeding the character limit, so I’ll show only the bits that I added:
- When
InputObjectis passed as an explicit parameter:
if ($InputObject -is [System.Data.IDataReader]) {
$dbReaderFieldCount = $InputObject.FieldCount
$dateOrTimeColumnNumberformats = @()
for ($j = 0; $j -lt $dbReaderFieldCount; $j++) {
if ($InputObject.GetFieldType($j) -eq [DateTime]) {
$dateOrTimeColumnNumberformats += 'Date-Time'
}
elseif ($InputObject.GetFieldType($j) -eq [TimeSpan]) {
$dateOrTimeColumnNumberformats += '[h]:mm:ss'
}
else {
$dateOrTimeColumnNumberformats += ''
}
}
if ($Append -and $ws.dimension) {
$row++
$null = $ws.Cells[$row, $StartColumn].LoadFromDataReader($InputObject, $false )
if ($TableName -or $PSBoundParameters.ContainsKey('TableStyle')) {
Add-ExcelTable -Range $ws.Cells[$ws.Dimension] -TableName $TableName -TableStyle $TableStyle -TableTotalSettings $TableTotalSettings
}
}
else {
if ($null -ne $TableName) {
while ($TableName -in $pkg.Workbook.Worksheets.Tables.name) {
Write-Warning "Table name $($InputObject.TableName) is not unique, adding '_' to it "
$TableName += "_"
}
}
if ($null -ne $TableName -or $PSBoundParameters.ContainsKey("TableStyle")) {
$null = $ws.Cells[$row, $StartColumn].LoadFromDataReader($InputObject, (-not $noHeader), $TableStyle )
if ($ws.Dimension.End.Rows -eq 0) {
($ws.Tables | Select-Object -Last 1).TableXml.table.SetAttribute('insertRow', 1)
}
}
else {
$null = $ws.Cells[$row, $StartColumn].LoadFromDataReader($InputObject, (-not $noHeader) )
}
}
for ($j = 0; $j -lt $dbReaderFieldCount; $j++) {
if ($dateOrTimeColumnNumberformats[$j].Length -gt 0) {
Set-ExcelColumn -Worksheet $ws -Column ($j + $StartColumn) -NumberFormat $dateOrTimeColumnNumberformats[$j]
}
}
$ColumnIndex = $dbReaderFieldCount
if ($noHeader) { $row = $ws.Dimension.End.Row - 1 }
else { $row = $ws.Dimension.End.Row }
$null = $PSBoundParameters.Remove('InputObject')
$firstTimeThru = $false
}
- When
InputObjectis passed via the pipeline:
if ( $InputObject -is [System.Data.IDataRecord] ) {
if ($firstTimeThru) {
$firstTimeThru = $false
$Script:Header = @()
$dbReaderFieldCount = $InputObject.FieldCount
for ($j = 0; $j -lt $dbReaderFieldCount; $j++) {
$Script:Header += $InputObject.GetName($j)
}
if ($DisplayPropertySet -and ($null -ne $ExcludeProperty) -and ($ExcludeProperty.Length -gt 0) ) {
$Script:Header = $Script:Header | Where-Object { $_ -notin $ExcludeProperty }
}
if ( ($null -ne $ExcludeProperty) -and ($ExcludeProperty.Length -gt 0)) {
foreach ($exclusion in $ExcludeProperty) {
$Script:Header = $Script:Header -notlike $exclusion
}
}
if ($NoHeader) {
$row--
}
else {
$ColumnIndex = $StartColumn
foreach ($Name in $Script:Header) {
$ws.Cells[$row, $ColumnIndex].Value = $Name
Write-Verbose "Cell '$row`:$ColumnIndex' add header '$Name'"
$ColumnIndex++
}
}
}
$row++
$ColumnIndex = $StartColumn
foreach ($Name in $Script:Header) {
$j = $InputObject.GetOrdinal($Name)
try {
if (!$InputObject.IsDBNull($j)) {
$fieldType = $InputObject.GetFieldType($j)
$fieldValue = $null
switch ($fieldType) {
{ $_ -is [Int32] } {
$fieldValue = $InputObject.GetInt32($j); break
}
{ $_ -is [String] } {
$fieldValue = $InputObject.GetString($j); break
}
{ $_ -is [Boolean] } {
$fieldValue = $InputObject.GetBoolean($j); break
}
{ $_ -is [DateTime] } {
$fieldValue = $InputObject.GetDateTime($j); break
}
{ $_ -is [TimeSpan] } {
$fieldValue = [TimeSpan]$InputObject.GetValue($j); break
}
{ $_ -is [Decimal] } {
$fieldValue = $InputObject.GetDecimal($j); break
}
{ $_ -is [Double] } {
$fieldValue = $InputObject.GetDouble($j); break
}
{ $_ -is [Boolean] } {
$fieldValue = $InputObject.GetBoolean($j); break
}
{ $_ -is [Float] } {
$fieldValue = $InputObject.GetFloat($j); break
}
{ $_ -is [Single] } {
$fieldValue = $InputObject.GetFloat($j); break
}
{ $_ -is [Int64] } {
$fieldValue = $InputObject.GetInt64($j); break
}
{ $_ -is [Int16] } {
$fieldValue = $InputObject.GetInt16($j); break
}
{ $_ -is [Byte] } {
$fieldValue = [int]$InputObject.GetByte($j); break
}
{ $_ -is [Char] } {
$fieldValue = $InputObject.GetChar($j).ToString(); break
}
{ $_ -is [Guid] } {
$fieldValue = $InputObject.GetGuid($j).ToString(); break
}
{ $_ -is [Object] } {
$fieldValue = $InputObject.GetValue($j); break
}
default {
throw "Unsupported field type: $($fieldType.FullName)"
}
}
if ($fieldValue -is [DateTime]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
$ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = 'm/d/yy h:mm'
}
elseif ($fieldValue -is [TimeSpan]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
$ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = '[h]:mm:ss'
}
elseif ($fieldValue -is [System.ValueType]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
}
elseif ($fieldValue -isnot [String]) {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue.ToString()
}
elseif ($fieldValue[0] -eq '=') {
$ws.Cells[$row, $ColumnIndex].Formula = ($fieldValue -replace '^=', '')
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
}
else {
if ( $NoHyperLinkConversion -ne '*' -and
$NoHyperLinkConversion -notcontains $Name -and
[System.Uri]::IsWellFormedUriString($fieldValue, [System.UriKind]::Absolute)
) {
if ($fieldValue -match "^xl://internal/") {
$referenceAddress = $fieldValue -replace "^xl://internal/" , ""
$display = $referenceAddress -replace "!A1$" , ""
$h = New-Object -TypeName OfficeOpenXml.ExcelHyperLink -ArgumentList $referenceAddress , $display
$ws.Cells[$row, $ColumnIndex].HyperLink = $h
}
else { $ws.Cells[$row, $ColumnIndex].HyperLink = $fieldValue }
$ws.Cells[$row, $ColumnIndex].Style.Font.Color.SetColor([System.Drawing.Color]::Blue)
$ws.Cells[$row, $ColumnIndex].Style.Font.UnderLine = $true
}
else {
$number = $null
if ( $NoNumberConversion -ne '*' -and
$numberRegex.IsMatch($fieldValue) -and
$NoNumberConversion -notcontains $Name -and
[Double]::TryParse($fieldValue, [System.Globalization.NumberStyles]::Any, [System.Globalization.NumberFormatInfo]::CurrentInfo, [Ref]$number)
) {
$ws.Cells[$row, $ColumnIndex].Value = $number
if ($setNumformat) { $ws.Cells[$row, $ColumnIndex].Style.Numberformat.Format = $Numberformat }
}
else {
$ws.Cells[$row, $ColumnIndex].Value = $fieldValue
}
}
}
}
}
catch { Write-Warning -Message "Could not insert the '$Name' property at Row $row, Column $ColumnIndex" }
$ColumnIndex++
}
}