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;

For a while i thought that in order to consume a custom SSIS task you had to install the assembly in the GAC. Now i know better

For the designer (BIDS) you have to copy the files to:

C:\Program Files\Microsoft SQL Server\100\DTS\Tasks
C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents

For the runtime (BIDS) you have to copy the files to:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

And for dtexec you have to copy the files to:

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Here is a small powershell script that gives you the paths where you want to copy your assembly to:

# Lookup Tasks, PipelineComponents and DtExec paths
$DtsPath = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\100\DTS\Setup').SQLPath;
$DtsTasksPath = (Resolve-Path "$DtsPath\Tasks");
$DtsPipelineComponentsPath = (Resolve-Path "$DtsPath\Pipelinecomponents");
$DtExecPath = (Resolve-Path "$DtsPath\Binn");

# Lookup VS2008/Bids path
$Vs2008Path = (Get-ItemProperty 'HKLM:\Software\Microsoft\VisualStudio\9.0').InstallDir;
$Vs2008PrivateAssembliesPath = (Resolve-Path "$Vs2008Path\PrivateAssemblies");

Here are some functions that allow you to update ConfigurationStrings in a SSIS package (dtsx) using PowerShell:

function UpdateConfigurationStrings($file)
{
 $xml = 1 (Get-Content $file);
 $ns = New-Object Xml.XmlNamespaceManager $xml.NameTable;
 $ns.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts");
 UpdateConfigurationNodes $xml $ns;
 Set-Content $file $xml.OuterXml;
}

function UpdateConfigurationNodes($xml, $ns)
{
 $selectConfigurationNodes = "//DTS:Configuration";
 $xml.SelectNodes($selectConfigurationNodes, $ns) | foreach { UpdateConfigurationNode $_ $ns; };
}

function UpdateConfigurationNode($configurationNode, $ns)
{   
 $selectConfigurationString = "./DTS:Property[@DTS:Name='ConfigurationString']";
 $configurationStringNode = $configurationNode.SelectSingleNode($selectConfigurationString, $ns);
    
 $oldConfigurationStringValue = $configurationStringNode.'#text';
 $newConfigurationStringValue = GetNewConfigurationStringValue $oldConfigurationStringValue;
 $configurationStringNode.'#text' = "$newConfigurationStringValue"; 
}

function GetNewConfigurationStringValue($oldConfigurationStringValue)
{
 # implement some logic to determine new value
 return "new value";
}