I have two variables (a simple string - where $strVMPrepDir = 'PrepareSQL' and an array $SQLParameters = an array I imported in from excel
SQLParameters = Import-Excel -Path $DataSourcePath -WorksheetName 'SQL'
where the array looks like
@{AGTSVCPASSWORD=password; ISSVCPASSWORD=password; ASSVCPASSWORD=password; SQLSVCPASSWORD=password; RSSVCPASSWORD=password; SAP
WD=password}
Or in its native form
$SQLParameters.AGTSVCPASSWORD = password
$SQLParameters.ISSVCPASSWORD=password
etc
etc
I am trying to pass these variable over to the VM with Invoke-Command with the following
$InstallSQL = {
$copyFileLocation = "C:\$args[0]\ConfigurationFile.ini"
$SQLConfigurationINI = "C:\$args[0]\ConfigurationFile2.ini"
$errorOutputFile = "C:\$args[0]\ErrorOutput.txt"
$standardOutputFile = "C:\$args[0]\StandardOutput.txt"
Write-Host "Getting the name of the current user to replace in the copy ini file." -ForegroundColor Green
$user = "$env:UserDomain\$env:USERNAME"
Write-Host "Update credential information to SQL Configuration file" -ForegroundColor Green
#$replaceText = (Get-Content -path $copyFileLocation -Raw) -replace "##MyUser##", $user
#Set-Content $copyFileLocation $replaceText
Get-Content $copyFileLocation | ForEach-Object { $_ `
-replace '<!--REPLACE WITH SQL AGENT PASSWORD-->',"$args[1].AGTSVCPASSWORD" `
-replace '<!--REPLACE WITH INTEGRATION SERVICES PASSWORD-->',"$args[1].ISSVCPASSWORD" `
-replace '<!--REPLACE WITH ANALYSIS SERVICES PASSWORD-->',"$args[1].ASSVCPASSWORD" `
-replace '<!--REPLACE WITH SQL SERVICES PASSWORD-->',"$args[1].SQLSVCPASSWORD" `
-replace '<!--REPLACE WITH SQL SA PASSWORD-->',"$args[1].SAPWD" `
-replace '<!--REPLACE WITH REPORTING SERVICES PASSWORD-->',"$args[1].RSSVCPASSWORD" `
} | Set-Content $SQLConfigurationINI
Push-Location -Path R:\
write-host $SQLConfigurationINI
.\Setup.exe /ConfigurationFile=$SQLConfigurationINI
# Configure inbound firewall rule for SQL Write-Host "Update Windows Firewall for inbound SQL..." -ForegroundColor Green New-NetFirewallRule -DisplayName "MSSQL ENGINE TCP" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
}
Invoke-Command -ComputerName $strVMName -ScriptBlock $InstallSQL -Credential $DCLocalCredential -ArgumentList $strVMPrepDir,$SQLParameters
Except the output is not what I expect, for example
$copyFileLocation = "C:\$args[0]\ConfigurationFile.ini", I was expecting this
$copyFileLocation = "C:\PrepareSQL\ConfigurationFile.ini" but I am getting this instead
$copyFileLocation = "C:\PrepareSQL [0]\ConfigurationFile.ini" (with a space after the L)
Also, my output for "$args[1].AGTSVCPASSWORD", I was expecting this
password, but I am getting this as output instead
[1].AGTSVCPASSWORD
I have used the $args with invoke-command before, but only for a single simple string (e.g. -ArgumentList $strVMPrepDir) and never try it as 2 strings, nor an array before. Is this possible, or just my syntax is off?
You are not passing an array of strings but an array of PSObjects.
You have to adapt the Param definition and how to reference the param in your code block
$test = {
param(
[string]$SingleVallue,
[PSObject[]]$Array
)
$SingleVallue
$Array[2].F1
}
$v1 = 'abc'
$v2 = @{F1='a';F2='b'},@{F1='k';F2='l'},@{F1='x';F2='y'}
Invoke-Command -ScriptBlock $test -ArgumentList $v1,@($v2)
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Did you already try with
-ArgumentList $strVMPrepDir,(,$SQLParameters)
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Do I keep the syntax for reading it the same ?
When I have multiple arguments to pass, I do it something like this.
Makes it easier to define the args in the codebloc.
$test = {
param(
[string]$SingleVallue,
[string[]]$Array
)
$SingleVallue
$Array[2]
}
$v1 = 'abc'
$v2 = 'x123','y456','z789'
Invoke-Command -ScriptBlock $test -ArgumentList $v1,@($v2)
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
1st suggestion did not work with the argumentlist, let me tried the second now
OK, some good news, I simple my code and did this
$strPrepDir = "PrepareSQL"
$SQLParameters = Import-Excel -Path $DataSourcePath -WorksheetName 'SQL'
note: this is what the actual worksheet looks like in excel
$InstallSQL = {
param(
[string]$SingleValue,
[string[]]$Array
)
$copyFileLocation = "C:\$SingleValue\ConfigurationFile.ini"
$SQLConfigurationINI = "C:\$SingleValue\ConfigurationFile2.ini"
$errorOutputFile = "C:\$SingleValue\ErrorOutput.txt"
write-host $SQLConfigurationINI
write-host $errorOutputFile
write-host $Array[2]
Invoke-Command -ComputerName $strVMName -ScriptBlock $InstallSQL -Credential $DCLocalCredential -ArgumentList $strVMPrepDir,@($SQLParameters)
Based on the output, the 1st ArgumentList variable pass over without issue as my output was correct
C:\PrepareSQL\ConfigurationFile2.ini
C:\PrepareSQL\ErrorOutput.txt
It replaced the $SingleValue with the actual value of $strVMPrepDir that I passed over to Invoke-Command. However, I would expect $array[2] to output the 2nd array value of 'password', in my case the output was blank
Quick update. I tried with the following and this was my output
$InstallSQL = {
param(
[string]$SingleValue,
[string[]]$Array
)
$copyFileLocation = "C:\$SingleValue\ConfigurationFile.ini"
$SQLConfigurationINI = "C:\$SingleValue\ConfigurationFile2.ini"
$errorOutputFile = "C:\$SingleValue\ErrorOutput.txt"
write-host $SQLConfigurationINI #### output as expect C:\PrepareSQL\ConfigurationFile2.ini
write-host $errorOutputFile #### output as expect C:\PrepareSQL\ErrorOutput.txt
write-host "$Array" ### output as expect ,as it gave me the entire array @{AGTSVCPASSWORD=password; ISSVCPASSWORD=password; ASSVCPASSWORD=password; SQLSVCPASSWORD=password; RSSVCPASSWORD=password; SAPWD=password}
$temp = "$Array"
write-host $temp ### output as expect ,as it gave me the entire array @{AGTSVCPASSWORD=password; ISSVCPASSWORD=password; ASSVCPASSWORD=password; SQLSVCPASSWORD=password; RSSVCPASSWORD=password; SAPWD=password}
write-host $temp.AGTSVCPASSWORD ### output was blank, not what I expect
write-host $temp(1) ### output was @{AGTSVCPASSWORD=password; ISSVCPASSWORD=password; ASSVCPASSWORD=password; SQLSVCPASSWORD=password; RSSVCPASSWORD=password; SAPWD=password} 1, not what I expect at all
C:\PrepareSQL\ConfigurationFile2.ini
C:\PrepareSQL\ErrorOutput.txt
@{AGTSVCPASSWORD=$vbe#26r; ISSVCPASSWORD=$vbe#26r; ASSVCPASSWORD=$vbe#26r; SQLSVCPASSWORD=$vbe#26r; RSSVCPASSWORD=$vbe#26r; SAP
WD=$SQLP@ssw0rd}
@{AGTSVCPASSWORD=$vbe#26r; ISSVCPASSWORD=$vbe#26r; ASSVCPASSWORD=$vbe#26r; SQLSVCPASSWORD=$vbe#26r; RSSVCPASSWORD=$vbe#26r; SAP
WD=$SQLP@ssw0rd}
@{AGTSVCPASSWORD=$vbe#26r; ISSVCPASSWORD=$vbe#26r; ASSVCPASSWORD=$vbe#26r; SQLSVCPASSWORD=$vbe#26r; RSSVCPASSWORD=$vbe#26r; SAP
WD=$SQLP@ssw0rd} 1
You are not passing an array of strings but an array of PSObjects.
You have to adapt the Param definition and how to reference the param in your code block
$test = {
param(
[string]$SingleVallue,
[PSObject[]]$Array
)
$SingleVallue
$Array[2].F1
}
$v1 = 'abc'
$v2 = @{F1='a';F2='b'},@{F1='k';F2='l'},@{F1='x';F2='y'}
Invoke-Command -ScriptBlock $test -ArgumentList $v1,@($v2)
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
let me ask you this, given my dataset within in the PSObject is
@{AGTSVCPASSWORD=password; ISSVCPASSWORD=password; ASSVCPASSWORD=password; SQLSVCPASSWORD=password; RSSVCPASSWORD=password; SAPWD=password}
Technically, isn't this just an object, and not an array?
If you are passing the result of Import-Excel row by row, then yes, that is a PSObject.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference