CODEWITHSUNDEEP
sqlsql-serverpowershell
virkboy
virkboy

Reputation: 17

Storing Results from Powershell to SQL Table

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

which is then called through powershell by the following command

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

$applications

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

But stuck in how this will be done

Upvotes: 0

Views: 2507

Answers (1)

Rigerta
Rigerta

Reputation: 4039

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

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/

You could do: 

$appsDataTable = $applications | Out-DataTable

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)

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

Upvotes: 1

Related Questions