Tim Van Wassenhove

Passionate geek, interested in Technology. Proud father of two

19 May 2011

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 am 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;