piercj2
Enthusiast
Enthusiast

Insert Array contents into SQL DB

Jump to solution

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 = @()

DatevCenterClusterVM_NameIP AddressPowerState
01/01/2020MyVC1MyCluster1MyVMname11.1.1.1PoweredOn
02/02/2020MyVC1MyCluster2MyVMName22.2.2.2PoweredOff
03/03/2020MyVC2AnotherClusterAnotherVMname3.3.3.3PoweredOn

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

DatevCenterClusterVM_NameIP_AddressPowerState
01/01/2020 02/02/2020 03/03/2020MyVC1 MyVC1 MyVC2MyCluster1 MyCluster2 AnotherClusterMyVMname1 MyVMname2 AnotherVMname1.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

Tags (3)
1 Solution

Accepted Solutions
LucD
Leadership
Leadership

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

}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

0 Kudos
5 Replies
LucD
Leadership
Leadership

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

}


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

0 Kudos
piercj2
Enthusiast
Enthusiast

Thanks Luc,

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

Thank you

0 Kudos
LucD
Leadership
Leadership

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

piercj2
Enthusiast
Enthusiast

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 ?

0 Kudos
LucD
Leadership
Leadership

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference