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:

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

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

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

Further reading

PoSH->Data Transformation -> SQL Table

Happy Learning!!