Integration Services (SSIS) 变量Integration Services (SSIS) Variables

适用对象:是SQL Server,包含 Linux 版 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server, including on Linux yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

变量存储 SQL ServerSQL Server Integration ServicesIntegration Services 包及其容器、任务和事件处理程序在运行时可以使用的值。Variables store values that a SQL ServerSQL Server Integration ServicesIntegration Services package and its containers, tasks, and event handlers can use at run time. 脚本任务和脚本组件中的脚本也可以使用变量。The scripts in the Script task and the Script component can also use variables. 将任务和容器按顺序组织为工作流的优先约束在其约束定义包含表达式时可以使用变量。The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

可以将 Integration ServicesIntegration Services 包中的变量用于下列目的:You can use variables in Integration ServicesIntegration Services packages for the following purposes:

  • 在运行时更新包元素的属性。Updating properties of package elements at run time. 例如,可以动态设置 Foreach 循环容器所允许的并发可执行文件数。For example, you can dynamically set the number of concurrent executables that a Foreach Loop container allows.

  • 包含内存中的查找表。Including an in-memory lookup table. 例如,包可以运行加载带数据值的变量的执行 SQL 任务。For example, a package can run an Execute SQL task that loads a variable with data values.

  • 加载带数据值的变量,然后使用这些变量指定 WHERE 子句中的搜索条件。Loading variables with data values and then using them to specify a search condition in a WHERE clause. 例如,脚本任务中的脚本可以更新执行 SQL 任务中的 Transact-SQL 语句所使用的变量的值。For example, the script in a Script task can update the value of a variable that is used by a Transact-SQL statement in an Execute SQL task.

  • 加载带整数的变量,然后使用该值控制包控制流中的循环。Loading a variable with an integer and then using the value to control looping within a package control flow. 例如,可以在 For 循环容器的计算表达式中使用变量来控制迭代。For example, you can use a variable in the evaluation expression of a For Loop container to control iteration.

  • 在运行时填充 Transact-SQL 语句的参数值。Populating parameter values for Transact-SQL statements at run time. 例如,包可以运行执行 SQL 任务,然后使用变量动态设置 Transact-SQL 语句中的参数。For example, a package can run an Execute SQL task and then use variables to dynamically set the parameters in a Transact-SQL statement.

  • 生成包含变量值的表达式。Building expressions that include variable values. 例如,派生列转换可以用变量值乘以列值所得结果来填充列。For example, the Derived Column transformation can populate a column with the result obtained by multiplying a variable value by a column value.

系统变量和用户定义变量System and user-defined variables

Integration ServicesIntegration Services 支持两种类型的变量:用户定义变量和系统变量。supports two types of variables: user-defined variables and system variables. 用户定义变量由包开发人员定义,系统变量由 Integration ServicesIntegration Services定义。User-defined variables are defined by package developers, and system variables are defined by Integration ServicesIntegration Services. 可以创建包所需数量的用户定义变量,但不能另外创建系统变量。You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

在执行 SQL 任务用来在 SQL 语句中将变量映射到参数的参数绑定中,可以使用所有变量(系统和用户定义)。All variables-system and user-defined-can be used in the parameter bindings that the Execute SQL task uses to map variables to parameters in SQL statements. 有关详细信息,请参阅 执行 SQL 任务执行 SQL 任务中的参数和返回代码For more information, see Execute SQL Task and Parameters and Return Codes in the Execute SQL Task.

备注

用户定义变量和系统变量的名称是区分大小写的。The names of user-defined and system variables are case sensitive.

可以为所有 Integration ServicesIntegration Services 容器类型创建用户定义变量,这些容器类型包括包、Foreach 循环容器、For 循环容器、序列容器、任务以及事件处理程序。You can create user-defined variables for all Integration ServicesIntegration Services container types: packages, Foreach Loop containers, For Loop containers, Sequence containers, tasks, and event handlers. 用户定义变量是容器中 Variables 集合的成员。User-defined variables are members of the Variables collection of the container.

使用 SSISSSIS 设计器创建包时, 设计器 “包资源管理器” 选项卡上的 “变量” SSISSSIS 文件夹中可以看到 Variables 集合的成员。If you create the package using SSISSSIS Designer, you can see the members of the Variables collections in the Variables folders on the Package Explorer tab of SSISSSIS Designer. 这些文件夹列出了用户定义变量和系统变量。The folders list user-defined variables and system variables.

可以使用下列方式配置用户定义变量:You can configure user-defined variables in the following ways:

  • 提供变量的名称和说明。Provide a name and description for the variable.

  • 指定变量的命名空间。Specify a namespace for the variable.

  • 指示变量是否在其值更改时引发事件。Indicate whether the variable raises an event when its value changes.

  • 指示变量是只读还是读/写。Indicate whether the variable is read-only or read/write.

  • 使用表达式的计算结果设置变量值。Use the evaluation result of an expression to set the variable value.

  • 在包或包对象(如任务)的范围内创建变量。Create the variable in the scope of the package or a package object such as a task.

  • 指定变量的值和数据类型。Specify the value and data type of the variable.

对系统变量唯一可配置的选项是指定变量在更改值时是否引发事件。The only configurable option on system variables is specifying whether they raise an event when they change value.

不同的容器类型有一组不同的系统变量可用。A different set of system variables is available for different container types. 有关包及其元素所使用的系统变量的详细信息,请参阅 System VariablesFor more information about the system variables used by packages and their elements, see System Variables.

有关变量的实际使用情况的详细信息,请参阅 在包中使用变量For more information about real-life use scenarios for variables, see Use Variables in Packages.

变量属性Properties of variables

你可通过在“变量”窗口或“属性”窗口中设置以下属性来配置用户定义变量 。You can configure user-defined variables by setting the following properties in either the Variables window or the Properties window. 某些属性仅在“属性”窗口中提供。Certain properties are available only in the Properties window.

备注

对系统变量唯一可配置的选项是指定变量在更改值时是否引发事件。The only configurable option on system variables is specifying whether they raise an event when they change value.

Description Description
指定对变量的描述。Specifies the description of the variable.

EvaluateAsExpression EvaluateAsExpression
将此属性设置为 True时,所提供的表达式可用于设置变量值。When the property is set to True, the expression provided is used to set the variable value.

表达式 Expression
指定分配给该变量的表达式。Specifies the expression that is assigned to the variable.

名称 Name
指定变量名称。Specifies the variable name.

命名空间Namespace
Integration ServicesIntegration Services 提供了两个命名空间: UserSystemprovides two namespaces, User and System. 默认情况下,自定义变量位于 User 命名空间中,系统变量位于 System 命名空间中。By default, custom variables are in the User namespace, and system variables are in the System namespace. 你可以为用户定义变量创建其他命名空间,并可以更改 User 命名空间的名称,但不能更改 System 命名空间的名称,也不能向 System 命名空间添加变量或将系统变量分配给其他命名空间。You can create additional namespaces for user-defined variables and change the name of the User namespace, but you cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.

RaiseChangedEventRaiseChangedEvent
将此属性设置为 True时,变量值的改变将会引发 OnVariableValueChanged 事件。When the property is set to True, the OnVariableValueChanged event is raised when the variable changes value.

ReadOnlyReadOnly
将此属性设置为 False时,该变量可读\写。When the property is set to False, the variable is read\write.

范围Scope

备注

可通过单击“变量”窗口中的“移动变量”来更改此属性设置 。You can change this property setting only by clicking Move Variable in the Variables window.

变量在包的作用域内或者包中的容器、任务或事件处理程序的作用域内创建。A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. 因为包容器位于容器层次结构的顶部,所以包作用域内的变量所起作用类似于全局变量,而且这些变量可由包中所有容器使用。Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. 同样,在 For 循环容器等容器的范围内定义的变量可由 For 循环容器中所有任务或容器使用。Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.

如果包使用执行包任务运行其他包,则可以使用父包变量配置类型,使在调用的包或执行包任务范围内定义的变量可供被调用的包使用。If a package runs other packages by using the Execute Package task, the variables defined in the scope of the calling package or the Execute Package task can be made available to the called package by using the Parent Package Variable configuration type. 有关详细信息,请参阅 Package ConfigurationsFor more information, see Package Configurations.

IncludeInDebugDumpIncludeInDebugDump
指示调试转储文件中是否包括变量值。Indicate whether the variable value is included in the debug dump files.

对于用户定义变量和系统变量, InclueInDebugDump 选项的默认值为 trueFor user-defined variables and system variables, the default value for the InclueInDebugDump option is true.

但是,对于用户定义变量,在满足以下条件时系统会将 IncludeInDebugDump 选项重置为 falseHowever, for user-defined variables, the system resets the IncludeInDebugDump option to false when the following conditions are met:

  • 如果 EvaluateAsExpression 变量属性设置为 true,则系统将 IncludeInDebugDump 选项重置为 falseIf the EvaluateAsExpression variable property is set to true, the system resets the IncludeInDebugDump option to false.

    若要在调试转储文件中包括表达式文本作为变量值,请将 IncludeInDebugDump 选项设置为 trueTo include the text of the expression as the variable value in the debug dump files, set the IncludeInDebugDump option to true.

  • 如果变量数据类型更改为字符串,则系统将 IncludeInDebugDump 选项重置为 falseIf the variable data type is changed to a string, the system resets the IncludeInDebugDump option to false.

在系统将 “IncludeInDebugDump” 选项重置为 false时,该设置可能会覆盖用户选择的值。When the system resets the IncludeInDebugDump option to false, this might override the value selected by the user.

“值” Value
用户定义变量的值可以是文字或表达式。The value of a user-defined variable can be a literal or an expression. 变量值不能为 null。The value of a variable can't be null. 变量具有以下默认值:Variables have the following default values:

数据类型Data type 默认值Default value
BooleanBoolean FalseFalse
数字和二进制数据类型Numeric and binary data types 0(零)0 (zero)
字符型和字符串数据类型Char and string data types (空字符串)(empty string)
ObjectObject System.ObjectSystem.Object

变量包含设置变量值和值的数据类型的选项。A variable has options for setting the variable value and the data type of the value. 这两种属性必须兼容:例如,字符串值不能与整数数据类型一起使用。The two properties must be compatible: for example, the use of a string value together with an integer data type is not valid.

如果将变量配置为作为表达式进行计算,则必须提供表达式。If the variable is configured to evaluate as an expression, you must provide an expression. 在运行时计算表达式,而该变量将设置为计算结果。At run time, the expression is evaluated, and the variable is set to the evaluation result. 例如,如果变量使用表达式 DATEPART("month", GETDATE()) ,则变量的值与当前日期月份部分的数字相同。For example, if a variable uses the expression DATEPART("month", GETDATE()) the value of the variable is the number equivalent of the month for the current date. 表达式必须是使用 SSISSSIS 表达式语法的有效表达式。The expression must be a valid expression that uses the SSISSSIS expression grammar syntax. 表达式和变量一起使用时,表达式可以使用文字以及表达式语法所提供的运算符和函数,但表达式不能引用包中数据流的列。When an expression is used with variables, the expression can use literals and the operators and functions that the expression grammar provides, but the expression cannot reference the columns from a data flow in the package. 表达式的最大长度为 4000 个字符。The maximum length of an expression is 4000 characters. 有关详细信息,请参阅 Integration Services (SSIS) 表达式For more information, see Integration Services (SSIS) Expressions.

ValueTypeValueType

备注

此属性值出现在“变量”窗口中的“数据类型”列中 。The property value appears in the Data type column in the Variables window.

指定变量值的数据类型。Specifies the data type of the variable value.

变量使用方案Scenarios for using variables

变量在 Integration ServicesIntegration Services 包中有多种不同的使用方式。Variables are used in many different ways in Integration ServicesIntegration Services packages. 您会发现,如果不向包中添加用户定义的变量以实现解决方案所要求的灵活性和可管理性,包的开发将无法进行下去。You will probably find that package development does not progress far before you have to add a user-defined variable to your package to implement the flexibility and manageability your solution requires. 根据方案的不同,通常还会用到系统变量。Depending on the scenario, system variables are also commonly used.

属性表达式 :在设置包和包对象属性的属性表达式中使用变量来提供值。Property Expressions Use variables to provide values in the property expressions that set the properties of packages and package objects. 例如,表达式 SELECT * FROM @varTableName 中包含变量 varTableName ,该变量可更新“执行 SQL 任务”所运行的 SQL 语句。For example, the expression, SELECT * FROM @varTableName includes the variable varTableName that updates the SQL statement that an Execute SQL task runs. 表达式 DATEPART("d", GETDATE()) == 1? @[User::varPackageFirst]:@[User::varPackageOther]" 通过在月份的第一天运行 varPackageFirst 变量指定的包而在其他天中运行 varPackageOther 变量指定的包,来更新“执行包”任务所运行的包。The expression, DATEPART("d", GETDATE()) == 1? @[User::varPackageFirst]:@[User::varPackageOther]", updates the package that the Execute Package task runs, by running the package specified in the varPackageFirst variable on the first day of the month and running the package specified in the varPackageOther variable on other days. 有关详细信息,请参阅 在包中使用属性表达式For more information, see Use Property Expressions in Packages.

数据流表达式 :在派生列转换和有条件拆分转换用于填充列的表达式中使用变量来提供值,或将数据行定向到不同的转换输出中。Data Flow Expressions Use variables to provide values in the expressions that the Derived Column and Conditional Split transformations use to populate columns, or to direct data rows to different transformation outputs. 例如,表达式 @varSalutation + LastName连接 VarSalutation 变量和 LastName 列中的值。For example, the expression, @varSalutation + LastName, concatenates the value in the VarSalutation variable and the LastName column. 表达式 Income < @HighIncomeIncome 列值小于 HighIncome 变量值的数据行定向到一个输出。The expression, Income < @HighIncome, directs data rows in which the value of the Income column is less than the value in the HighIncome variable to an output. 有关详细信息,请参阅 派生列转换有条件拆分转换Integration Services (SSIS) 表达式For more information, see Derived Column Transformation, Conditional Split Transformation, and Integration Services (SSIS) Expressions.

优先约束表达式 :提供要在优先约束中用来确定受约束的可执行文件是否运行的值。Precedence Constraint Expressions Provide values to use in precedence constraints to determine whether a constrained executable runs. 这些表达式可以和执行结果(成功、失败、完成)一起使用,也可代替执行结果。The expressions can be used either together with an execution outcome (success, failure, completion), or instead of an execution outcome. 例如,如果表达式 @varMax > @varMin的计算结果为 true,则运行可执行文件。For example, if the expression, @varMax > @varMin, evaluates to true, the executable runs. 有关详细信息,请参阅将表达式添加到优先约束For more information, see Add Expressions to Precedence Constraints.

参数和返回代码 :为输入参数提供值,或存储输出参数和返回代码的值。Parameters and Return Codes Provide values to input parameters, or store the values of output parameters and return codes. 可通过将变量映射到参数和返回值来执行上述操作。You do this by mapping the variables to parameters and return values. 例如,如果将变量 varProductId 设置为 23 并运行 SQL 语句 SELECT * from Production.Product WHERE ProductID = ?,查询将检索 ProductID 为 23 的产品。For example, if you set the variable varProductId to 23 and run the SQL statement, SELECT * from Production.Product WHERE ProductID = ?, the query retrieves the product with a ProductID of 23. 有关详细信息,请参阅 执行 SQL 任务执行 SQL 任务中的参数和返回代码For more information, see Execute SQL Task and Parameters and Return Codes in the Execute SQL Task.

For 循环表达式 :提供要在 FOR 循环的初始化表达式、求值表达式和赋值表达式中使用的值。For Loop Expressions Provide values to use in the initialization, evaluation, and assignment expressions of the For Loop. 例如,如果变量 varCount 为 2, varMaxCount 为 10,初始化表达式为 @varCount,求值表达式为 @varCount < @varMaxCount,赋值表达式为 @varCount =@varCount +1,则循环将重复 8 次。For example, if the variable varCount is 2 and varMaxCount is 10, the initialization expression is @varCount, the evaluation expression is @varCount < @varMaxCount, and the assignment expression is @varCount =@varCount +1, then the loop repeats 8 times. 有关详细信息,请参阅 For 循环容器For more information, see For Loop Container.

父包变量配置 :将值由父包传递给子包。Parent Package Variable Configurations Pass values from parent packages to child packages. 子包可通过使用父包变量配置来访问父包中的变量。Child packages can access variables in the parent package by using parent package variable configurations. 例如,如果子包必须与父包使用相同日期,则子包可以定义一个父包变量配置,该配置将指定 GETDATE 函数在父包中设置的变量。For example, if the child package must use the same date as the parent package, the child package can define a parent package variable configuration that specifies a variable set by the GETDATE function in the parent package. 有关详细信息,请参阅 Execute Package TaskPackage ConfigurationsFor more information, see Execute Package Task and Package Configurations.

脚本任务和脚本组件 为脚本任务或脚本组件提供只读和可读/写变量的列表,在脚本内更新读/写变量,然后在该脚本内或该脚本外使用更新的值。Script Task and Script Component Provide a list of read-only and read/write variable to the Script task or Script component, update the read/write variables within the script, and then use the updated values in or outside the script. 例如,在代码 numberOfCars = CType(Dts.Variables("NumberOfCars").Value, Integer)中,脚本变量 numberOfCarsNumberOfCars变量中的值更新。For example, in the code, numberOfCars = CType(Dts.Variables("NumberOfCars").Value, Integer), the script variable numberOfCars is updated by the value in the variable, NumberOfCars. 有关详细信息,请参阅 Using Variables in the Script TaskFor more information, see Using Variables in the Script Task.

添加变量Add a variable

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开要使用的 Integration ServicesIntegration Services 包。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services package you want to work with.

  2. 在解决方案资源管理器中,双击该包将其打开。In Solution Explorer, double-click the package to open it.

  3. SSISSSIS 设计器中,若要定义变量的作用域,请执行下列操作之一:In SSISSSIS Designer, to define the scope of the variable, do one of the following:

    • 若要设置包的范围,请单击 “控制流” 选项卡设计图面上的任何位置。To set the scope to the package, click anywhere on the design surface of the Control Flow tab.

    • 若要设置事件处理程序的作用域,请在 “事件处理程序” 选项卡的设计图面上,选择一个可执行文件和一个事件处理程序。To set the scope to an event handler, select an executable and an event handler on the design surface of the Event Handler tab.

    • 若要设置任务或容器的范围,请在 “控制流” 选项卡或 “事件处理程序” 选项卡的设计图面上,单击一个任务或容器。To set the scope to a task or container, on the design surface of the Control Flow tab or the Event Handler tab, click a task or container.

  4. SSIS 菜单上单击 “变量”On the SSIS menu, click Variables. 您可以通过将 View.Variables 命令映射到在 “选项” 对话框的 “键盘” 页上选择的组合键来显示 “变量” 窗口。You can optionally display the Variables window by mapping the View.Variables command to a key combination of your choosing on the Keyboard page of the Options dialog box.

  5. “变量” 窗口中,单击 “添加变量” 图标。In the Variables window, click the Add Variable icon. 新的变量将添加到该列表中。The new variable is added to the list.

  6. 也可以单击 “网格选项” 图标,选择要在 “变量网格选项” 对话框中显示的其他列,然后单击 “确定”Optionally, click the Grid Options icon, select additional columns to show in the Variables Grid Options dialog box, and then click OK.

  7. 或者,设置变量属性。Optionally, set the variable properties. 有关详细信息,请参阅 设置用户定义变量的属性For more information, see Set the Properties of a User-Defined Variable.

  8. 若要保存更新后的包,请单击 “文件” 菜单上的 “保存选定项”To save the updated package, click Save Selected Items on the File menu.

“添加变量”对话框Add Variable dialog box

可以使用 “添加变量” 对话框指定新变量的属性。Use the Add Variable dialog box to specify the properties of a new variable.

选项Options

容器Container
在列表中选择容器。Select a container in the list. 容器定义了变量的作用域。The container defines the scope of the variable. 容器可以是包,也可以是包中的可执行文件。The container can be either the package or an executable in the package.

名称Name
键入变量名称。Type the variable name.

NamespaceNamespace
指定变量的命名空间。Specify the namespace of the variable. 默认情况下,用户定义变量位于 User 命名空间中。By default, user-defined variables are in the User namespace.

值类型Value type
选择数据类型。Select a data type.

ReplTest1Value
键入值。Type a value. 该值必须与 “值类型” 选项中指定的数据类型相符。The value must be compatible with the data type specified in the Value type option.

只读Read-only
如果选择此项,则变量将是只读的。Select to make the variable read-only.

删除变量Delete a variable

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在解决方案资源管理器中,右键单击该包将其打开。In Solution Explorer, right-click the package to open it.

  3. SSIS 菜单上单击 “变量”On the SSIS menu, click Variables. 您可以通过将 View.Variables 命令映射到在 “选项” 对话框的 “键盘” 页上选择的组合键来显示 “变量” 窗口。You can optionally display the Variables window by mapping the View.Variables command to a key combination of your choosing on the Keyboard page of the Options dialog box.

  4. 选择要删除的变量,然后单击 “删除变量”Select the variable to delete, and then click Delete Variable.

    如果在“变量”窗口中没有看到该变量,请单击“网格选项”,然后选择“显示所有作用域的变量” 。If you don't see the variable in the Variables window, click Grid Options and then select Show variables of all scopes.

  5. 如果 “确认删除变量” 对话框打开,请单击 “是” 确认删除。If the Confirm Deletion of Variables dialog box opens, click Yes to confirm.

  6. 若要保存更新后的包,请单击 “文件” 菜单上的 “保存选定项”To save the updated package, click Save Selected Items on the File menu.

更改变量的作用域Change the scope of a variable

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在解决方案资源管理器中,右键单击该包将其打开。In Solution Explorer, right-click the package to open it.

  3. SSIS 菜单上单击 “变量”On the SSIS menu, click Variables. 您可以通过将 View.Variables 命令映射到在 “选项” 对话框的 “键盘” 页上选择的组合键来显示 “变量” 窗口。You can optionally display the Variables window by mapping the View.Variables command to a key combination of your choosing on the Keyboard page of the Options dialog box.

  4. 选择该变量,然后单击 “移动变量”Select the variable and then click Move Variable.

    如果在“变量”窗口中没有看到该变量,请单击“网格选项”,然后选择“显示所有作用域的变量” 。If you don't see the variable in the Variables window, click Grid Options and then select Show variables of all scopes.

  5. “选择新作用域” 对话框中,选择包或包中的容器、任务或事件处理程序,以更改变量作用域。In the Select New Scope dialog box, select the package or a container, task, or event handler in the package, to change the variable scope.

  6. 若要保存更新后的包,请单击 “文件” 菜单上的 “保存选定项”To save the updated package, click Save Selected Items on the File menu.

设置用户定义变量的属性Set the properties of a user-defined variable

若要在 Integration ServicesIntegration Services中设置用户定义的变量的属性,可以使用下列功能之一:To set the properties of a user-defined variable in Integration ServicesIntegration Services, you can use one of the following features:

  • “变量”窗口。Variables window.

  • 属性窗口。Properties window. “属性” 窗口列出了用于配置“变量” 窗口中未提供的变量的属性:Description、EvaluateAsExpression、Expression、ReadOnly、ValueType 和 IncludeInDebugDump。The Properties window lists properties for configuring variables that are not available in the Variables window: Description, EvaluateAsExpression, Expression, ReadOnly, ValueType, and IncludeInDebugDump.

备注

Integration ServicesIntegration Services 还提供了一组无法更新属性的系统变量,但 RaiseChangedEvent 属性例外。also provides a set of system variables whose properties cannot be updated, with the exception of the RaiseChangedEvent property.

对变量设置表达式Set expressions on variables

使用 “属性”窗口对用户定义变量设置表达式时:When you use the Properties window to set expressions on a user-defined variable:

  • 可通过 Value 属性或 Expression 属性来设置变量的值。The value of a variable can be set by the Value or the Expression property. 默认情况下,EvaluateAsExpression 属性设置为 False ,变量的值由 Value property 属性进行设置。By default, the EvaluateAsExpression property is set to False and the value of the variable is set by the Value property. 若要使用表达式来设置值,必须首先将 EvaluateAsExpression 设置为 True,然后在 Expression property 属性中提供一个表达式。To use an expression to set the value, you must first set EvaluateAsExpression to True, and then provide an expression in the Expression property. Value 属性自动设置为该表达式的计算结果。The Value property is automatically set to the evaluation result of the expression.

  • ValueType 属性包含 Value 属性中的值的数据类型。The ValueType property contains the data type of the value in the Value property. 通过表达式设置 Value 时,ValueType 将自动更新为与该表达式的计算结果兼容的数据类型。When Value is set by an expression, ValueType is automatically updated to a data type that is compatible with the evaluation result of the expression. 例如,如果 Value 包含 0,ValueType 属性包含 Int32 ,并将 Expression 设置为 GETDATE(),则 Value 包含当前日期和时间,并且 ValueType 会设置为 DateTimeFor example, if Value contains 0 and ValueType property contains Int32 and you then set Expression to GETDATE(), Value contains the current date and time and ValueType is set to DateTime.

  • 通过变量的 “属性” 窗口,可以访问 “表达式生成器” 对话框。The Properties window for the variable provides access to the Expression Builder dialog box. 使用该工具可以生成、验证和计算表达式。You can use this tool to build, validate, and evaluate expressions. 有关详细信息,请参阅表达式生成器Integration Services (SSIS) 表达式For more information, see Expression Builder and Integration Services (SSIS) Expressions.

使用 “变量”窗口对用户定义变量设置表达式时:When you use the Variables window to set expressions on a user-defined variable:

  • 若要使用表达式来设置变量值,首先请确认变量数据类型与表达式的计算结果一致,然后在 “变量” 窗口的 “表达式” 列中提供表达式。To use an expression to set the variable value, first confirm that the variable data type is compatible with the evaluation result of the expression and then provide an expression in the Expression column of the Variables window. “属性” 窗口中的 EvaluateAsExpression 属性会自动设置为 TrueThe EvaluateAsExpression property in the Properties window is automatically set to True.

  • 如果为变量指定了表达式,则该变量旁边将显示一个特殊图标标记。When you assign an expression to a variable, a special icon marker displays next to the variable. 这个特殊的图标标记还显示在设置有表达式的连接管理器和任务旁边。This special icon marker also displays next to connection managers and tasks that have expressions set on them.

  • 通过变量的 “变量” 窗口,可以访问 “表达式生成器” 对话框。The Variables window for the variable provides access to the Expression Builder dialog box. 使用该工具可以生成、验证和计算表达式。You can use this tool to build, validate, and evaluate expressions. 有关详细信息,请参阅表达式生成器Integration Services (SSIS) 表达式For more information, see Expression Builder and Integration Services (SSIS) Expressions.

“变量”“属性” 窗口中,如果为变量分配了表达式,并且 EvaluateAsExpression 设置为 True,则无法更改变量数据类型。In both the Variables and Properties window, if you assign an expression to the variable, and EvaluateAsExpression is set to True, you cannot change the variable data type.

设置 Namespace 和 Name 属性Set the Namespace and Name properties

NameNamespace 属性的值必须以 Unicode 标准 2.0 定义的字母字符或下划线 () 开头。The values of the Name and Namespace properties must begin with an alphabetic character letter as defined by the Unicode Standard 2.0, or an underscore (). 后续字符可以是在 Unicode 标准 2.0 中定义的字母或数字,或是下划线 (_)。Subsequent characters can be letters or numbers as defined in the Unicode Standard 2.0, or the underscore (_).

在变量窗口中设置变量属性Set Variable Properties in the Variables Window

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在解决方案资源管理器中,右键单击该包将其打开。In Solution Explorer, right-click the package to open it.

  3. SSIS 菜单上单击 “变量”On the SSIS menu, click Variables.

    您可以通过将 View.Variables 命令映射到在 “选项” 对话框的 “键盘” 页上选择的组合键来显示 “变量” 窗口。You can optionally display the Variables window by mapping the View.Variables command to a key combination of your choosing on the Keyboard page of the Options dialog box.

  4. 或者,在 “变量” 窗口中单击 “网格选项” ,然后选择要出现在 “变量” 窗口中的列,并选择要应用到变量列表的筛选器。Optionally, in the Variables window click Grid Options, and then select the columns to appear in the Variables window and select the filters to apply to the list of variables.

  5. 在列表中选择变量,然后更新 “名称”“数据类型”“值”“命名空间”“引发更改事件”“描述”“表达式” 列的值。Select the variable in the list, and then update values in the Name, Data Type, Value, Namespace, Raise Change Event, Description, and Expression columns.

  6. 在列表中选择变量,然后单击 “移动变量” 以更改作用域。Select the variable in the list, and then click Move Variable to change the scope.

  7. 若要保存已更新的包,请在 “文件” 菜单中单击 “保存选定项”To save the updated package, on the File menu, click Save Selected Items.

在属性窗口中设置变量属性Set Variable Properties in the Properties Window

  1. SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT)中,打开包含所需包的 Integration ServicesIntegration Services 项目。In SQL Server Data Tools (SSDT)SQL Server Data Tools (SSDT), open the Integration ServicesIntegration Services project that contains the package you want.

  2. 在解决方案资源管理器中,右键单击该包将其打开。In Solution Explorer, right-click the package to open it.

  3. “视图” 菜单上,单击 “属性窗口”On the View menu, click Properties Window.

  4. SSISSSIS 设计器中,单击 “包资源管理器” 选项卡,并展开“包”节点。In SSISSSIS Designer, click the Package Explorer tab and expand the Package node.

  5. 若要修改包范围内的变量,请展开“变量”节点,如果看不到该节点,请展开“事件处理程序”或“可执行文件”节点,直到找到包含要修改的变量的“变量”节点。To modify variables with package scope, expand the Variables node; otherwise, expand the Event Handlers or Executables nodes until you locate the Variables node that contains the variable that you want to modify.

  6. 单击要修改其属性的变量。Click the variable whose properties you want to modify.

  7. 在 “属性”窗口中,更改读/写变量属性。In the Properties window, update the read/write variable properties. 对于用户定义的变量而言,某些属性为可读/只读。Some properties are read/read only for user-defined variables.

    有关属性的详细信息,请参阅 Integration Services (SSIS) 变量For more information about the properties, see Integration Services (SSIS) Variables.

  8. 若要保存已更新的包,请在 “文件” 菜单中单击 “保存选定项”To save the updated package, on the File menu, click Save Selected Items.

使用配置以动态方式更新变量Update a variable dynamically with configurations

若要动态更新变量,可以为变量创建配置,将这些配置部署到包中,然后在部署包时更新配置文件中的变量值。To dynamically update variables, you can create configurations for the variables, deploy the configurations with the package, and then update the variable values in the configuration file when you deploy the packages. 在运行时,包使用更新后的变量值。At run time, the package uses the updated variable values. 有关详细信息,请参阅 创建包配置For more information, see Create Package Configurations.

在子包中使用变量和参数的值Use the Values of Variables and Parameters in a Child Package

将查询参数映射到数据流组件中的变量Map Query Parameters to Variables in a Data Flow Component