Using and setting variables in your packages

Using and setting variables in your package

You can use variables in most components and fields of a package. You cannot use, for example, variables in the limit component or in alias fields.

Variable values are expressions (except secrets, which are only string) so you can use functions and operators to assign dynamic values to your variables.

Variable values are evaluated only at runtime (when a package is ran), so you must replace these variables with actual values for ETL components (like the Schema step) to work in the package designer.

You can use three types of variables:

  • Package variables that you define. Value is an expression. You also set the default value.
  • Secret variables for sensitive values such as tokes, API keys passwords etc. Value is a string. 
  • System variables for which you can change the default value. Value is an expression. See list here.
  • Variables predefined by ETL, whose values are set by the system when the job is run. See list here.
  • Global variables (account level). Value is an expression. Details is here.

Using variables in a variable or a field:

In the required field, type $ followed by the variable name. For example, if the variable name is country, type $country. Note that variables are simply substituted with their values. Therefore if you use a variable where a string value is expected, you should enclose it in single quotes, as in this example: SUBSTRING('$country',3,5)

Using variables in a workflow package:

User variables can be defined at the workflow package level, as well, and can be used for both the Execute SQL Task and the Run Package Task. However, be aware that we address package variables in an Execute SQL Task differently than we described above (i.e. $variable_name.)  When using variables in a SQL query, enclose the variable within curly brackets (i.e: '${var_name}'). See this document for more information.

Setting variable values in your package

Set package and system variable default values in the package designer. If required, you can override these default values when you run a job through the UI (see Running jobs), the scheduler or the API.

To modify a system variable default value in the package:

  1. Click ...(Set variables)
  2. Click system variables.
  3. Type a new default value in the relevant text box.

You can also use the following in system variable values:

  • Predefined variables
  • Another system variable that is listed before the system variable whose value you're editing.

To define a package variable and set its value:

  1. Click ...(Set variables)
  2. On the package variables tab, type a name and a default value in the relevant text boxes.
  3. Add additional variables as required.

You can also use the following in package variable values:

  • Predefined variables
  • System variables
  • Another package variable that is listed before the package variable whose value you're editing.

To override global variables values in the package:

  1. Type a name of global variable(s) you want to override under package variables tab.
  2. Set its value.