Insert Array contents into SQL DB

[piercj2]

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

[LucD · Accepted solution]

Did you already try passing the array row by row and declaring the parameters?

function Out-SQL {

    [CmdletBinding()]

    param(

        [string]$vCenter,

        [string]$Cluster,

        [string]$VMName,

        [string]$IPAddr,

        [string]$PowerState

    )

    $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', '$vCenter', '$Cluster', '$VMName', '$IPAddr', '$PowerState');"

    #echo $SQL_Property

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

}

$VMinfo | ForEach-Object -process {

    Out_SQL $_.vCenter $_.Cluster $_.'VM Name' $_.'IP Address' $_.PowerState

}

[piercj2]

Thanks Luc,

I don't understand the mechanics of what you did but, it worked for me

Thank you

[LucD]

I used most of your code, but I feed the array row by row to SQL function.
And I defined parameters on the function.

[piercj2]

Hi Luc,

one question to help my understanding if you don't mind.

in the Out-SQL Function,

• I understand declaring the Parameters in the beginning
• I understand the SQL Connection String variables
• I don't understand the $output_* variables, as far as i can understand, they are not used/called anywhere ?

        $output_Date = $VMinfo.Date

        $output_vCenter = $VMinfo.vCenter

        $output_Cluster = $VMinfo.Cluster

Can they be removed from the function or, am i totally missing their purpose ?

[LucD]

No, you are right, I forgot to remove those.