6 methods to write PowerShell output to a SQL Server table

PowerShell has become the ultimate choice for many database administrators because of its efficient way of handling and managing automation in a simple, quick way. It’s built on .NET Framework and uses Object Models such as COM, ADSI, ADO, and WMI. PowerShell has replaced the traditional way of scripting that used many legacy scripting practices to monitor SQL instances.

I’ve been asked on several occasions about how to store the output of PowerShell WMI data into the SQL table. The question comes up so frequently that I decided to write this article.

When sending data within a system (such as a PowerShell object to a cmdlet), the process is straightforward. However, with non-native data interchange (for instance, WMI to SQL), the process can potentially get complicated. Due to this, many purists suggest sticking to simple interchange formats, such as CSV, JSON or in some cases, XML.

Let’s get out and see the possible options to transform WMI data to SQL table. In this article, we will:

discuss Invoke-Sqlcmd
talk about the .NET class libraries
talk about exporting data using various Export* cmdlets
learn how to use Windows Management Instrumentation (WMI)
discuss SQL Constructs to load data from file
and more

This guide details the working example of checking disk space by querying WMI.

Microsoft brought in WMI in order to simplify the management of the different classes of operating systems. While automation was one of the reasons for bringing WMI, the primary focus was to provide developers with handles that they could use when creating applications for Windows. But of course, WMI has these nifty uses as well.

The simplest way to get the disk space information is with the Win32_LogicalDisk class. We filter the content to pick only DriveType=3 which is the type number for local drives.

1 2	Get-WmiObject win32_logicaldisk -ComputerName <ComputerName> -Filter "Drivetype=3" \|` select SystemName,DeviceID,VolumeName,@{Label="Total Size";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }}\|Format-Table -AutoSize

We discuss the transformation of the above data into a SQL Table using some direct as well as indirect methods in this post:

using Invoke-Sqlcmd
using ADO
WMI Query
using Export commands such as JSON,XML and CSV

Pre-requisites

Before we proceed, let’s look at what we need before we can proceed:

SSMS 17.2
SQL Server PowerShell module

Getting started

Let’s now start transforming the data we received, into a SQL table.

Invoke-Sqlcmd

The Invoke-Sqlcmd is a wrapper class and PowerShell version of SQL Server sqlcmd command with additional capabilities such as data manipulation and data transformations with a focus on the output data.

The process is pretty simple:

create PoShDisk Table

CREATE TABLE tbl_PoShDisk

(

[SystemName] VARCHAR(40) not null,

[DeviceID] VARCHAR(40) not null,

[VolumeName] VARCHAR(40) not null,

[TotalSize] int not null,

[FreeSize] int not null

)

import the SqlServer Module
declare the variable and the connection string
define the function which prepares WMI data for SQL insertion
query the Win32_LogicalDisk class of WMI
loop through the result-set
insert the data into the SQL Table

Let us prepare the script

#Declare Servername

$sqlServer='hqdbsp18'

#Invoke-sqlcmd Connection string parameters

$params = @{'server'='HQDBT01';'Database'='SQLShackDemo'}

#Fucntion to manipulate the data

Function writeDiskInfo

{

param($server,$devId,$volName,$frSpace,$totSpace)

$totSpace=[math]::Round(($totSpace/1073741824),2)

$frSpace=[Math]::Round(($frSpace/1073741824),2)

$usedSpace = $totSpace - $frspace

$usedSpace=[Math]::Round($usedSpace,2)

# Data preparation for loading data into SQL table

$InsertResults = @"

INSERT INTO [SQLShackDemo].[dbo].[tbl_PosHdisk](SystemName,DeviceID,VolumeName,TotalSize,FreeSize)

VALUES ('$SERVER','$devId','$volName',$totSpace,$usedSpace)

#call the invoke-sqlcmdlet to execute the query

Invoke-sqlcmd @params -Query $InsertResults

}

#Query WMI query to store the result in a varaible

$dp = Get-WmiObject win32_logicaldisk -ComputerName $sqlServer| Where-Object {$_.drivetype -eq 3}

#Loop through array

foreach ($item in $dp)

{

#Call the function to transform the data and prepare the data for insertion

writeDiskInfo $sqlServer $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size

}

#Query the destination table to view the result

Invoke-Sqlcmd @params -Query "SELECT * FROM tbl_PosHdisk" | format-table -AutoSize

The below screen shot shows the steps and the results. The output is queried from the table tbl_PoShDisk

There we go; we see how the data from the WMI query was transferred to an SQL Table

ADO.NET objects

Another method to execute a query in PowerShell is to use ADO.NET libraries, which requires creating a DataSet, then creating a DataAdapter, and finally filling the DataAdapter. For many data retrieval needs from scripts ADO.NET can be a little too heavy. Fortunately, there are several ways to make this task simpler and still retain the benefits of the .NET DataTables. Let’s look at three methods to get SQL Server data from PowerShell. Once you have the data in DataTable, we can transform the data into a number of things including piping the output to one of the built-in cmdlets.

ADO.NET is a set of class libraries that are part of the .NET Framework. The ADO.NET classes are generally divided into two types: connected classes and disconnected classes.

Connected class

SqlConnection – connects to the SQL Server .NET data provider in order to establish and manage the connection to the target database
SqlCommand – contains the details necessary to issue a T-SQL command against a SQL Server database
SqlDataAdapter – provides a bridge between the connected classes and disconnected classes. This class includes the Fill and Update methods. Use the Fill method to populate a DataSet or DataTable object. Use the Update method to propagate updated data in a DataSet or DataTable object to the database
SqlBulkCopy – efficiently bulk loads a SQL Server table with data from another source

Disconnected classes

DataTable – stores the data returned by your query. The data is stored in rows and columns, similar to how data is stored in a database table

Script preparation

#Invoke-sqlcmd Connection string parameters

$params = @{'server'='HQDBT01';'Database'='SQLShackDemo'}

#Server to query WMI class win32_logicalDisks

$server = 'hqdbsp18'

#Prepare Insert Statement

$insert = @'

INSERT INTO [SQLShackDemo].[dbo].[tbl_PosHdisk](SystemName,DeviceID,VolumeName,TotalSize,FreeSize)

VALUES ('{0}','{1}','{2}','{3}','{4}')

Try {

#Define connction string of target database

$connectionString = 'Data Source=HQDBT01;Initial Catalog=SQLShackDemo;Integrated Security=SSPI'

# connection object initialization

$conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)

#Open the Connection

$conn.Open()

# Prepare the SQL

$cmd = $conn.CreateCommand()

#WMI ouput transformation to SQL table

Get-WmiObject win32_logicaldisk -ComputerName $server -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="TotalSize";Expression={$_.Size / 1gb -as [int] }},@{Label="FreeSize";Expression={$_.freespace / 1gb -as [int] }}|`

ForEach-Object{

$cmd.CommandText = $insert -f $_.SystemName, $_.DeviceID, $_.VolumeName, $_.TotalSize, $_.FreeSize

$cmd.ExecuteNonQuery()

}

#Close the connection

$conn.Close()

}

Catch {

Throw $_

}

Invoke-Sqlcmd @params -Query "SELECT * FROM tbl_PosHdisk" | format-table -AutoSize

The output is given below

Using SqlBulkCopy

We write a function to perform the copy operation. The function, Out-DataTable can be found in Appendix (A). This function takes care of converting the output of the WMI query to the data table. The data-table output is then fed to the SqlBulkCopy class in order to write the data to the SQL table. The SqlBulkCopy class loads a SQL Server table with data from another source which in this case is Win32_LogicalDisks.

#Invoke-sqlcmd Connection string parameters

$params = @{'server'='HQDBT01';'Database'='SQLShackDemo'}

#function to retrieve disk information

Function Get-DisksSpace ([string]$Servername)

{

Get-WmiObject win32_logicaldisk -ComputerName $Servername -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }}

}

#Variable to hold output as data-table

$dataTable = Get-DisksSpace hqdbsp18 | Out-DataTable

#Define Connection string

$connectionString = "Data Source=hqdbt01; Integrated Security=True;Initial Catalog=SQLShackDemo;"

#Bulk copy object instantiation

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString

#Define the destination table

$bulkCopy.DestinationTableName = "tbl_PosHdisk"

#load the data into the target

$bulkCopy.WriteToServer($dataTable)

#Query the target table to see for output

Invoke-Sqlcmd @params -Query "SELECT * FROM tbl_PosHdisk" | format-table -AutoSize

Use PowerShell Export options with SQL constructs

We can also use SQL constructs with the PowerShell Export options. We can:

export the WMI data to JSON file and query using SQL native JSON constructs
export to XML data and query using Nodes()
export the data to CSV format and use SQL native Bulk-Insert command to query the data

Let’s look at these processes one by one.

Using JSON

The ConvertTo-Json cmdlet converts an object to a valid JSON string. Using this cmdlet, the output is converted to JSON (JavaScript Object Notation). The JSON file is then queried using JSON construct in SQL Server, called OPEN JSON, to transform the data form the win32_LogicalDisk WMI class, to a relational table.

The WMI output is stored in a variable which is then written to a JSON file using Out-File formatting cmdlet.

$JSON=Get-WmiObject win32_logicaldisk -ComputerName hqdbsp18 -Filter "Drivetype=3" |select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} |ConvertTo-Json

$json |Out-File \\hqdbt01\f$\PowerSQL\DiskSpace.JSON

The output of JSON file is shown below

Let’s now feed the JSON into an SQL table

SELECT t.*

FROM

OPENROWSET(BULK N'\\hqdbt01\f$\PowerSQL\DiskSpace.JSON', SINGLE_NCLOB) AS JSON

CROSS APPLY OPENJSON(BulkColumn)

WITH(

Server NVARCHAR(10),

DeviceID NVARCHAR(20),

VolumeName NVARCHAR(20),

[Total SIze] DECIMAL(5,2),

[Free Size] DECIMAL(5,2)

) AS t

The data can be fed to table using the Insert SQL statement

Using XML

The XML data comes from the XML file and is stored in a column bulkcolumn; use the XML methods, extract values with xml.value(), project nodes with xml.nodes(), use CROSS APPLY to join its nodes to derive all the values of the nodes.

The below query writes WMI output to XML File

Get-WmiObject win32_logicaldisk -ComputerName hqdbsp18 -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} |ConvertTo-Xml -as String -NoTypeInformation|`

Set-Content -path \\hqdbt01\f$\PowerSQL\DiskSpace.xml

The below is the generated XML file

load the data into temp table which holds XMLData as its column
use OPENROWSET clause to load XML data to single column
transform each nodes using XML value() and Query() method to derive its values

CREATE TABLE tbl_XMLDisk

(

XMLData XML,

)

--truncate table XMLwithOpenXML

INSERT INTO tbl_XMLDisk(XMLData)

SELECT

CONVERT(XML, BulkColumn) AS BulkColumn

FROM

OPENROWSET(BULK 'f:\PowerSQL\DiskSpace.xml', SINGLE_BLOB) AS x;

SELECT XMLData FROM tbl_XMLDisk

SELECT

p.value('(./Property)[1]', 'VARCHAR(20)') AS SystemName,

p.value('(./Property)[2]', 'VARCHAR(30)') AS DeviceID,

p.value('(./Property)[3]', 'VARCHAR(30)') AS VolumeName,

p.value('(./Property)[4]', 'int') AS [Total SIze],

p.value('(./Property)[5]', 'int') AS [Free Size]

FROM tbl_XMLDisk

CROSS APPLY XMLData.nodes('/Objects/Object') t(p)

The XML Query output

Using BULK INSERT

The first statement we’ll look at is BULK INSERT, which lets you import data from a data file into a table In the following example, I import the data from the CSV file into the SQL table

Get-WmiObject win32_logicaldisk -ComputerName hqdbsp18 -Filter "Drivetype=3" |`

select SystemName,DeviceID,VolumeName,@{Label="Total SIze";Expression={$_.Size / 1gb -as [int] }},@{Label="Free Size";Expression={$_.freespace / 1gb -as [int] }} |`

ConvertTo-Csv -NoTypeInformation| Set-Content -path \\hqdbt01\f$\PowerSQL\DiskSpace.csv

--Create table.

CREATE TABLE tbl_CSVDisk

(

[SystemName] VARCHAR(40),

[DeviceID] VARCHAR(40),

[VolumeName] VARCHAR(40),

[TotalSize] VARCHAR(40),

[FreeSize] VARCHAR(40)

)

--Load the data into the SQL table starting with 2 row, comma(‘,’) as delimiter and newline as

--rowseparator

BULK

INSERT tbl_CSVDisk

FROM 'F:\PowerSQL\DiskSpace.csv'

WITH

(

FIRSTROW = 2,

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

--Check the content of the table.

SELECT REPLACE(systemName,'"','') systemName,

REPLACE([DeviceID],'"','') [DeviceID],

REPLACE([VolumeName],'"','') [VolumeName],

REPLACE([TotalSize],'"','') [TotalSize],

REPLACE([FreeSize],'"','') [FreeSize]

FROM tbl_CSVDisk

Wrapping up

Different methods and various techniques can be used to achieve a specific result. The challenge is to use the right tool, the right way, for the right job: the proverbial “Driving a screw with a hammer” problem. When presented with a new tool set, we shouldn’t try and use it the same way as other tools we’ve had in the past. Instead, we must learn the tool so we can make the best use of it for your process.

In this case, we may have to consider the efficiency of the methods we listed, and make a decision about which method to use.

Appendix (A)

Function Out-DataTable

{

$dt = new-object Data.datatable

$First = $true

foreach ($item in $input){

$DR = $DT.NewRow()

$Item.PsObject.get_properties() | foreach {

if ($first) {

$Col = new-object Data.DataColumn

$Col.ColumnName = $_.Name.ToString()

$DT.Columns.Add($Col) }

if ($_.value -eq $null) {

$DR.Item($_.Name) = "[empty]"

}

elseif ($_.IsArray) {

$DR.Item($_.Name) =[string]::Join($_.value ,";")

}

else {

$DR.Item($_.Name) = $_.value

}

$DT.Rows.Add($DR)

$First = $false

}

return @(,($dt))

}

Author
Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

Stairway to SQL essentials - April 7, 2021
A quick overview of database audit in SQL - January 28, 2021
How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021

Pre-requisites

Getting started

Invoke-Sqlcmd

ADO.NET objects

Using SqlBulkCopy

Use PowerShell Export options with SQL constructs

Using JSON

Using XML

Using BULK INSERT

Wrapping up

Appendix (A)

Related posts: