如何在 SSIS 的 Power shell 脚本中添加参数和连接管理器 [英] How to add parameters and conn managers in Power shell script for SSIS

问题描述

下面是我的两个参数的power shell脚本1. 环境 2. ProcessData如果我在命令提示符下执行此操作，则会出现以下错误对于选项集无效.我如何解决这个问题请帮助是因为数据源中的逗号.数据源=xxxxxxx,3181;

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

推荐答案

根本问题是您为 User::ProcessData 传入的分号被解释为命令行参数的分隔符和不是字符串中的值.

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"

那会产生

参数 ""\Package.Variables[User::Environment].Properties[Value];[sql1811174];;选项设置"的 Dev"" 无效.

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;\""

为什么我可以对 Environment 使用简单的双引号，而不能对 ProcessData 使用?很好的问题，我不知道.我认为这与论证的复杂性有关 + 影响了前面的论证.

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.

这篇关于如何在 SSIS 的 Power shell 脚本中添加参数和连接管理器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！