Insert Array contents into SQL DB

This post was originally published on this site

HI All,

 

i realize that this is more a PowerShell question than PowerCLI but i’ve been trying to figure this our for a week now and am getting nowhere fast. Hoping someone on here may have done this already.

 

I have an Array with basic VM information $VMinfo = @()

Date vCenter Cluster VM_Name IP Address PowerState
01/01/2020 MyVC1 MyCluster1 MyVMname1 1.1.1.1 PoweredOn
02/02/2020 MyVC1 MyCluster2 MyVMName2 2.2.2.2 PoweredOff
03/03/2020 MyVC2 AnotherCluster AnotherVMname 3.3.3.3 PoweredOn

 

With the below function, i can insert the data into a SQL Database but, everything goes in on a single row, i.e.

Date vCenter Cluster VM_Name IP_Address PowerState
01/01/2020 02/02/2020 03/03/2020 MyVC1 MyVC1 MyVC2 MyCluster1 MyCluster2 AnotherCluster MyVMname1 MyVMname2 AnotherVMname 1.1.1.1 2.2.2.2 3.3.3.3 PoweredOn Power

 

function Out-SQL {

    [CmdletBinding()]

 

    $output_Server = “MySQLserver.domain.com”

    $output_Database = “My_Database”

    $output_Username = “myUsername”

    $output_Password = “myPassword!”

    

    $Date = Get-Date

    $output_vCenter = $VMinfo.vCenter

    $output_Cluster = $VMinfo.Cluster

    $output_VMname = $VMinfo.’VM Name’

    $output_IP = $VMinfo.’IP Address’

    $output_PowerState = $VMinfo.PowerState

   

    $SQL_Property = “USE $output_Database

            INSERT INTO [My_Database].[dbo].[VM_Info] (Date, vCenter, Cluster, VM_Name, IP_Address, PowerState)

            VALUES(‘$Date’, ‘$output_vCenter’, ‘$output_Cluster’, ‘$output_VMname’, ‘$output_IP’, ‘$output_PowerState’);”

    #echo $SQL_Property

    $SQL_Property_Insert = Invoke-Sqlcmd -Query $SQL_Property -ServerInstance $output_Server -Username $output_Username -Password $output_Password

}

$VMinfo | OUT_SQL

 

While this gets the data into SQL, the formatting of the table is bad. So i tried the following

 

$SQL_Info = $VMinfo

$Connection = New-Object System.Data.SQLClient.SQLConnection

$Connection.ConnectionString = “server=’$SQL_Server’;database=’$SQL_Database’;user=$SQL_Username;password=$SQL_Password”

$Connection.Open()

 

$command = New-Object System.Data.SqlClient.SqlCommand

$command.connection = $Connection

for($r=0; $r -lt $SQL_Info.Length; $r++){

    [datetime]$Date = (Get-Date).ToString($TimeTormat)

    $vCenter = $SQL_Info[$r].vCenter

    $Cluster = $SQL_Info[$r].Cluster

    $VM_Name = $SQL_Info[$r].’VM Name’

    $IP_Address = $SQL_Info[$r].’IP Address’

    $PowerState = $SQL_Info[$r].PowerState

 

    $SQL_Info_Query = “USE $SQL_Database

            INSERT INTO [My_Database].[dbo].[VM_Info] (Date, vCenter, Cluster, VM_Name, IP_Address, PowerState)

            VALUES(‘$Date’, ‘$vCenter’, ‘$Cluster’, ‘$VM_Name’, ‘$IP_Address’, ‘$PowerState’);”

    $command.CommandText = $SQL_Info_Query

    $command.ExecuteNonQuery()

}

 

The above just throws errors but, i think i need to break the array down row-by-row, treat each row as a PowerShell Custom Object and, add the properties of each object to the database, i.e Date, vCenter, Cluster, VM_Name, IP_Address & PowerState.

 

Has anyone done this or, able to offer any suggestions as to how if can complete it ?

Thanks

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.