Powershell Referring to Variable Dynamically

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:


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:

However it does not apear to work… Thoughts?


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:


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



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


1 Like

Hi Jeremy,

Yes, this works perfectly. Thankyou for your help!


1 Like

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.


This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.