Below is my power shell script for two parameters
1. Environment 2. ProcessData
if i execute this in the command prompt i am getting below error
For option set is not valid.
How i can resolve this issue pls help is it because of comma in Data Source.
Data Source=xxxxxxx,3181;
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;"
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.
