Powershell Referring to Variable Dynamically

[damien_taylor]

Hi All,

I am working on a script that will traverse a deployed directory for SQL scripts to run. There will be a sub directory for each database and each of these directories can have multiple .sql files.

Each different DB has a seperate connection string as defined in variables.
DBConnectionString_DB1 = Connection string for DB1
DBconnectionString_DB2 = Connection string for DB2

I am needing to dynamically select the appropriate variable depending on the database. Refer to script below:

---

Get-ChildItem -Path 'd:\temp\octopus\contents\sql' |

Foreach-Object {


    foreach($file in Get-ChildItem $_.FullName)
    {   
         Invoke-Sqlcmd -InputFile $file.FullName -ConnectionString #{DBConnectionString_$file.Directory.Name} 
    }

}

---

I have been building a powershell script within Octopus step. I am wishing to use a variable to specify the connection script for SQL command. However I have 2 variables:

DBConnectionString_DB1
DBconnectionString_DB2

The DB1 and DB2 should be substibuted for the currente directory. However when referencing the variable how can I dynamaically reference the appropriate variable?

I found a document that suggested the following:
#{DBConnectionString_$file.Directory.Name}

However it does not apear to work… Thoughts?

Thanks,
Damien

[jeremy.miller]

Hi Damien,

Thanks for reaching out.

To dynamically set an Octopus variable, the best way would be to define it as a variable, then use that variable.

You could use something similar to the below, but with your if logic added in for selecting the correct Octopus variable:

Code:

$one = "DB1"
$two = "DB2"
$string = $OctopusParameters["DBConnectionString_$($one)"]
$string
$string = $OctopusParameters["DBConnectionString_$($two)"]
$string

Variables:

Output:

Please let me know if that helps or if I misunderstood the question.

Thanks,
Jeremy

[damien_taylor]

Hi Jeremy,

Yes, this works perfectly. Thankyou for your help!

Damien

[jeremy.miller]

Hi Damien,

You’re very welcome, I’m glad to hear it’s working for you. Thanks for letting me know!

I hope you have a great weekend.

Thanks,
Jeremy