PowerShell Call SSIS package, Pass Two (or More) Parameters

I am writing a group of PowerShell scripts to call SSIS packages, passing in the SQL password obtained from an Azure key vault. When the SSIS package has a single connection it works just fine - this for instance:

    DTEXEC /FILE "./$ProcessName.dtsx" /CONFIGFILE "$ScriptDirectory\config\$env\$ProcessName.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /CONSOLELOG SM /REPORTING EWCDI /SET "\Package.Connections[DATABASE.INC.DB].Properties[Password];$secretpwd" > $SSISLog

what is the syntax if there are 2 or more databases to pass the password in for? I don't think I can include 2 SET statements

Solution

When the SSIS package has a single connection it works just fine. What is the syntax if there are 2 or more databases to pass the password in for?

Two Potential Solutions

#1

Setup an array with database names, then loop through each name to execute the DTEXEC command while injecting the database name and password into the command dynamically. This way handles multiple database connections without using multiple SET statements.

$dbs = @("DATABASE.INC.DB","DATABASE.INC.XY","DATABASE.INC.ABC");
foreach($db in $dbs){ 
    DTEXEC /FILE "./$ProcessName.dtsx" /CONFIGFILE "$ScriptDirectory\config\$env\$ProcessName.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /CONSOLELOG SM /REPORTING EWCDI /SET "\Package.Connections[$db].Properties[Password];$secretpwd" > $SSISLog
    ##Write-Host "DTEXEC /FILE `"./$ProcessName.dtsx`" /CONFIGFILE `"$ScriptDirectory\config\$env\$ProcessName.dtsConfig`" /MAXCONCURRENT `" -1 `" /CHECKPOINTING OFF /CONSOLELOG SM /REPORTING EWCDI /SET `"\Package.Connections[$db].Properties[Password];$secretpwd`" > $SSISLog"
    };

#2

Define an array of database names, then use ForEach-Object to create another array of /SET statements for each database. Join the /SET statements into a single string and concatenate to the DTEXEC command at execution using multiple /SET statements in one execution.

$dbs = @("DATABASE.INC.DB","DATABASE.INC.XY","DATABASE.INC.ABC");
$sets = $dbs | ForEach-Object {"/SET `"\Package.Connection[$_].Properties[Password];$($secretpwd)`""};
$joinSets = $sets -join " ";

DTEXEC /FILE "./$ProcessName.dtsx" /CONFIGFILE "$ScriptDirectory\config\$env\$ProcessName.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /CONSOLELOG SM /REPORTING EWCDI $joinSets > $SSISLog
##Write-Host "DTEXEC /FILE `"./$ProcessName.dtsx`" /CONFIGFILE `"$ScriptDirectory\config\$env\$ProcessName.dtsConfig`" /MAXCONCURRENT `" -1 `" /CHECKPOINTING OFF /CONSOLELOG SM /REPORTING EWCDI /SET $joinSets > $SSISLog"

/Set [$Sensitive::]propertyPath;value: (Optional). Overrides the configuration of a parameter, variable, property, container, log provider, Foreach enumerator, or connection within a package. When this option is used, /Set changes the propertyPath argument to the value specified. Multiple /Set options can be specified.

Source