Different methods to write PowerShell output to a SQL 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:

  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!!

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ http://codingsight.com/author/prashanthjayaram/ https://www.red-gate.com/simple-talk/author/prashanthjayaram/ http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ Connect Me: Twitter @prashantjayaram GMAIL powershellsql@gmail.com The articles are published in: http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL, T-SQL and tagged , , , , , . Bookmark the permalink.

2 Responses to Different methods to write PowerShell output to a SQL table

  1. Alleged DBA says:

    A very thorough and well-done piece. Thank you for sharing this helpful information with useful examples.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s