Running an SSIS package from PowerShell (using DTExec) can be as simple as:

RunPackage -File 'C:\test.dtsx' -DatabaseHost '.' -DatabaseName 'TEST';

Here are the functions that make it this simple:

function GetDtExecPath {
    $DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;
    $DtExecPath = (Resolve-Path "$DtsPath\Binn\DTExec.exe");
    $DtExecPath;
}

function GetDtExecPropertyPathValue() {
    param(
        $PropertyPath = '',
        $Value = ''
    );
    "$PropertyPath;\`"`"$Value\`"`"";
}

function RunPackage {
    param(
        $DtExecPath = (GetDtExecPath),
        $File = 'test.dtsx'
    );

    $Params = "/FILE $File";
    for($i = 0; $i -lt $Args.Length; $i += 2) {
        $PropertyPath = $Args[$i].SubString(1);
        $Value = $Args[$i+1];
        $PropertyPathValue = GetDtExecPropertyPathValue -PropertyPath $PropertyPath -Value $Value;
        $Params += " /SET $PropertyPathValue";
    } 

    &"$DtExecPath" $Params;
}

The documentation on dtexec Utility (SSIS Tool) says the following:

/Set propertyPath;value

(Optional). Overrides the configuration of a 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.

At first sight this works like a charm but as soon as your value has a space dtexec seems to get confused It took me a couple of websearches to find a post that suggests the following:

dtexec /SET \Package.Variables[User::TheVariable].Properties[Value];\”"; space”\”

It seems that this works like a charm Because i’m lazy i wrapped this in a powershell function:

function PackageOption()
{
	param($name, $value);
	"$name;\`"`"$value\`"`"";
}

And now you can use it as following in your deployment script:

$TheVariableOption = PackageOption -Name "\Package.Variables[User::TheVariable].Properties[Value]" -Value "some thing";
&dtexec /File "$package" /Set $TheVariableOption;