Prashanth Jayaram

6 methods to write PowerShell output to a SQL Server table

December 25, 2017 by

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.


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

  1. using Invoke-Sqlcmd
  2. using ADO
  3. WMI Query
  4. 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:

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:

  1. create PoShDisk Table
  2. import the SqlServer Module
  3. declare the variable and the connection string
  4. define the function which prepares WMI data for SQL insertion
  5. query the Win32_LogicalDisk class of WMI
  6. loop through the result-set
  7. insert the data into the SQL Table

Let us prepare the script

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

  1. SqlConnection – connects to the SQL Server .NET data provider in order to establish and manage the connection to the target database
  2. SqlCommand – contains the details necessary to issue a T-SQL command against a SQL Server database
  3. 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
  4. SqlBulkCopy efficiently bulk loads a SQL Server table with data from another source

Disconnected classes

  1. 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

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.

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.

The output of JSON file is shown below

Let’s now feed the JSON into an SQL table

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

The below is the generated XML file

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

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




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.

References

Appendix (A)


See more

Consider these free tools for SQL Server that improve database developer productivity.


Prashanth Jayaram

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
Prashanth Jayaram
PowerShell

About 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

8,089 Views