将PowerShell输出写入SQL Server表的6种方法

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.

PowerShell已成为许多数据库管理员的最终选择，因为它以简单，快速的方式有效地处理和管理自动化。 它基于.NET Framework构建，并使用对象模型，例如COM，ADSI，ADO和WMI。 PowerShell已取代了传统的脚本编写方式，该方法使用了许多旧式脚本编写实践来监视SQL实例。

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.

我曾多次被问到如何将PowerShell WMI数据的输出存储到SQL表中。 这个问题经常出现，我决定写这篇文章。

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.

在系统内发送数据（例如将PowerShell对象发送到cmdlet）时，该过程非常简单。 但是，使用非本地数据交换（例如，从WMI到SQL），该过程可能会变得很复杂。 因此，许多纯粹主义者建议坚持使用简单的交换格式，例如CSV，JSON或在某些情况下使用XML。

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

让我们出发，看看将WMI数据转换为SQL表的可能选项。 在本文中，我们将：

• discuss Invoke-Sqlcmd
  讨论Invoke-Sqlcmd
• talk about the .NET class libraries
  谈谈.NET类库
• talk about exporting data using various Export* cmdlets
  讨论使用各种Export * cmdlet导出数据
• learn how to use Windows Management Instrumentation (WMI)
  了解如何使用Windows Management Instrumentation（WMI）
• discuss SQL Constructs to load data from file
  讨论从文件加载数据SQL构造
• and more
  和更多

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

本指南详细介绍了通过查询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.

Microsoft引入了WMI，以简化对不同类别的操作系统的管理。 自动化是带来WMI的原因之一，但主要重点是为开发人员提供在创建Windows应用程序时可以使用的句柄。 但是当然，WMI也具有这些漂亮的用途。

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.

获取磁盘空间信息的最简单方法是使用Win32_LogicalDisk类。 我们过滤内容以仅选择DriveType = 3，这是本地驱动器的类型号。

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:

在本文中，我们将讨论使用一些直接方法和间接方法将上述数据转换为SQL表：

1. using Invoke-Sqlcmd
   使用Invoke-Sqlcmd
2. using ADO
   使用ADO
3. WMI Query
   WMI查询
4. using Export commands such as JSON,XML and CSV
   使用导出命令，例如JSON，XML和CSV

先决条件 (Pre-requisites)

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

在继续之前，让我们先看一下我们需要什么：

• SSMS 17.2 SSMS 17.2
• SQL Server PowerShell module
  SQL Server PowerShell模块

入门 (Getting started)

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

现在开始将接收到的数据转换为SQL表。

调用Sqlcmd (Invoke-Sqlcmd)

The Invoke-Sqlcmd<