asked on

How to Load the Output of a Powershell Function into a SQL Server table

I am using this PowerShell Function to get the local drive and mount point information from a Server. I need to use this function to retrieve information from many servers and then dump the output into a SQL Server table. I am not sure how to do this correctly. Below is the function I am using as well as some info I gathered but I am not sure how to put it all together. Any suggestions would be appreciated.

Function Get-DiskDrive
{

Param(
[string[]]$drive = $env:SystemDrive,
[string[]]$computername = $env:COMPUTERNAME
) #end param
Get-WmiObject -Class win32_Volume -ComputerName $computername |
Select-object DriveLetter, Label, FileSystem, PageFilePresent,
@{Name = "ComputerName"; Expression = {$_.__Server} },
@{Name = "Capacity(GB)"; Expression = {$_.capacity / 1GB} },
@{Name = "FreeSpace(GB)"; Expression = {$_.Freespace / 1GB} },
@{Name = "PercentFree"; Expression = { ($_.FreeSpace / $_.Capacity)*100 } }

} #end function get-diskdrive

---------------------------------

$serverlist = (Get-Content -path C:\ServerNames\Serverlist.txt)
foreach ($server in $serverlist)
{
Get-DisksSpace $server

$dataTable = Get-DisksSpace $server | out-DataTable
$connectionString = "Data Source=<server\instance>; Integrated Security=True;Initial Catalog=<databaseName>;"
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = "<schema.table>"
$bulkCopy.WriteToServer($dataTable)

ASKER CERTIFIED SOLUTION

x-men

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

All true. And in general, if you don't use the content of a var at least twice, don't use a var. That is

foreach ($server in Get-Content C:\ServerNames\ServerList.txt)

Open in new window

is to be preferred over

$serverlist = (Get-Content -path C:\ServerNames\Serverlist.txt)
foreach ($server in $serverlist)

Open in new window

$connectionString above is an exception, as the string is really long, and so using a var here makes the code more readable.