Set variables in SSIS package at runtime

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;

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>