Defines variables. Variables can be used to dynamically control script execution. You can store different types of values in variables. The syntax is:
Syntax
VAR[Variable name][Variable value]
Parameters
Variable name
The name of the variable. Use descriptive names.
Variable value
The value of the variable. The values can be of any type.
To reference a variable use the following syntax:
%VAR%
How to use a variable
1 In the Transform tab, choose “New”. Then choose VAR in the list of transformation instructions that appears. The Variable screen appears.
2 In the Name field, type the variable name, as shown in Figure 5‑56. In the Value field, type the variable value. In this example, the variable contains the name of a table.
Figure 5‑56 Creating a variable
3 Click “OK” to confirm your definition and go back to the main Transform screen. The new VARIABLE instruction appears in the Main file panel.
4 In the Transform tab, choose “New” and this time choose EXPRESSION in the transformation instructions list that appears. This opens the Expression screen.
5 In the first Table field, select a database from the drop-down list. Then type the following expression[%Table%] in the second Table field, as shown in Figure 5‑57:
Figure 5‑57 Using a variable
6 In the Column field, type the name of the new column.
7 In the Expression field, type the expression for calculating the value of the new column. Use the variable reference where needed.
[Demo].[%Table%].[Income]
8 Click “OK” to confirm you definition and go back to the main Transform screen.
9 Save your project.
10 Run the instruction. The log, as shown in Figure 5‑58, shows that the TABLE variable is replaced by its value, Customer.
Figure 5‑58 Run log
Managing the transformation process when exceptions occur
Users can decide not to stop the transformation process when errors occur via the TRANSFORM tab. By default, the loading process stops whenever an error is encountered.
Use the ONERROR instructions for the transformation script to choose whether you want the transformation process to stop in the case of error or to continue running. This enables the transformation process to ignore any columns that present errors during the process, leaving them to be fixed and processed later if desired.
ON ERROR
In the TRANSFORM tab, you can tell the loading process to make the one of the following actions when an error is detected:
continue
break
This is done by clicking on “New”, selecting “ON ERROR” from the list of available instructions that appears. Then choose either “continue” or “break” as needed. Use the “up” and “down” arrows at the top of the tab to position your instructions wherever you want them to be in your rows of instructions. Figure 5‑59 shows an on error instruction to continue.
Figure 5‑59 Managing exceptions using the Transform tab
continue