[Solved] How to add parameters and conn managers in Power shell script for SSIS

Question

The root issue is the semicolon you are passing in for User::ProcessData is being interpreted as a delimiter for command line parameters and not as value inside a string.

You can verify this behaviour by adding a semi-colon to the first property

dtexec /ISServer "\SSISDB\DEV\PopulateData\PopulateData.dtsx" /server abbaa.com,3181 /Par "$ServerOption::SYNCHRONIZED(Boolean)";True /SET \Package.Variables[User::Environment].Properties[Value];"[sql1811174];Dev"

That will generate

Argument “”\Package.Variables[User::Environment].Properties[Value];[sql1811174];; Dev”” for option “set” is not valid.

The completely unintuitive escaping approach is to add a leading slash to the double quotes for your parameter values and double them up.

dtexec /ISServer "\SSISDB\DEV\PopulateData\PopulateData.dtsx" 
/server abbaa.com,3181 /Par "$ServerOption::SYNCHRONIZED(Boolean)";True 
/SET \Package.Variables[User::Environment].Properties[Value];\"[sql1811174] Dev\" 
/SET \Package.Variables[User::ProcessData].Properties[Value];"\"Data Source=xxxxxxx,3181;Initial Catalog=xxx11;Provider=SQLNCLI11.1;Integrated Security=SSPI;\""

Why can I use a simple double quote for Environment but not for ProcessData? Great question and I have no idea. I assume it’s something with the complexity of the argument + affected the preceding arguments.

Accepted Answer