将结果从 Powershell 存储到 SQL 表 [英] Storing Results from Powershell to SQL Table

问题描述

我有一个 .ps1 脚本抓取每个应用程序池下的应用程序，如下

I have an .ps1 script grabbing applications under each app pool as follows

param([string]$appPool)    

import-module webadministration

$apps = (Get-WebConfigurationProperty 
"/system.applicationHost/sites/site/application[`
@applicationPool='$appPool']" "machine/webroot/apphost" -name path).ItemXPath 

$out = @()

foreach ($s in $apps) {
$name = $s -replace "\/system.applicationHost\/sites\/site\[\@name='", ""
$name = $name -replace "' and \@id='\d{1,10}'\]\/application\[\@path='", 
$name = $name -replace "'\]",""
$out += @{ 
appPool=$appPool;
location=$name
};

}

$out

然后通过以下命令通过powershell调用

which is then called through powershell by the following command

$applications = .\Get-AppsInAppPool.ps1 -appPool "ADTTest"

$applications

我的目标是将结果保存到 SQL 服务器数据库表中.

What I'm aiming to do is to save the results to a SQL server Database Table.

但坚持如何做到这一点

推荐答案

您可以将结果转换为数据表，然后在 PowerShell 中使用 Data.SqlClient.SqlBulkCopy 将其加载到您的数据库表中.

You can convert the result into a datatable and then load it into your database table using Data.SqlClient.SqlBulkCopy in PowerShell.

将结果转换为数据表的脚本已经开源:https://gallery.technet.microsoft.com/ScriptCenter/4208a159-a52e-4b99-83d4-8048468d29dd/

The script to convert your result into a datatable is already available open source: https://gallery.technet.microsoft.com/ScriptCenter/4208a159-a52e-4b99-83d4-8048468d29dd/

这是一篇出色的文章，可以指导您将数据加载到数据库表中:

And this is an excellent article that can guide you into loading your data in a database table:

https://blogs.technet.microsoft.com/heyscriptingguy/2011/11/28/four-easy-ways-to-import-csv-files-to-sql-server-with-powershell/

你可以这样做:

$appsDataTable = $applications | Out-DataTable

然后打开与 SQL Server 的连接并执行:

And then open a connection to SQL Server and do:

$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString 
$bulkCopy.DestinationTableName = $table 
$bulkCopy.BatchSize = $batchSize 
$bulkCopy.BulkCopyTimeout = $timeout 
$bulkCopy.WriteToServer($appsDataTable) 

当然，您需要为 $connectionString、$table 等赋值.

Of course you will need to give values to the $connectionString, $table etc.

这篇关于将结果从 Powershell 存储到 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！