Use Variables In Automation
Automation variables are a powerful tool that can greatly extend your scripts. Variables can add flexibility and customization. Variables can be used in almost every Automation activity.
How to Create Variables
User-Defined Variables
You can use the Set Variable activity to create user-defined variables. See Use System Automation Activities for more information.
Built-In Variables
Several Automation activities create variables by default. The following built-in variables are created by Automation activities.
-
RowCount variable— This integer type variable is created by several activities. The value assigned to this variable is the number of rows returned by the query in the activity.
- Return code variable— This integer type variable is created by the Run Automation Script and the Run Program activities. The variable value is assigned based on the execution status of the script or program.
- Built-in SQL type variables—The Loop Dataset and Execute Script activities create a SQL type variable which is used to store the result set of a query. The Loop Connections activity creates a SQL type variable with only one row containing connection information.
- Activity Result variable—This variable captures the result of each activity's execution. If an error occurs, it captures the error message. The name of this variable is _ACTIVITY_RESULT.
The Automation script creates the following built-in variables.
- Root path variable—This string type variable is created by the script. The value assigned to this variable is the path you specified in the Root path field in either the Test environment or the Production environment area in script Settings. The path that is applied depends on which environment (Test or Production) is selected at the time the script is executed.
- Run mode variable—This string type variable is created by the script. The value assigned to this variable is either Test or Production, depending on the environment selected in the script Settings at the time the script is executed.
How to Use Variables
Where to Use Variables
The following are some examples of where you can use a variable (built-in or user-defined) in an Automation script.
- In the file name of a file exported by an activity.
- In the suffix appended to a file name.
- In an expression used in the While or If Condition activities.
- In a SQL statement as a bind variable for filtering data.
- In a SQL statement as a bind variable in an Import or Export template.
How to Use a Variable
To express the value of a variable, surround the variable name with hash symbols (#), for example #myvar#. The following are some examples of how variables might be used.
#myvar#_report.xlsx
Report_#Dept_Name#.xlsx
#File_1_RCOUNT# > 0
It is important to remember that when a variable is expressed, the variable value is substituted as text in place of the variable name (#myvar#). The only exception is when you use a variable as a bind variable in a SQL statement.
To use a variable as a bind variable in a SQL statement, use the following syntax format (regardless of what the address_id column type is):
SELECT * FROM contact WHERE address_id = :myvar
You can also use the Automation variable here as text substitution, but in this case single quotes might be required (because it is a text substitution) depending on the column type in the WHERE clause.
For example, if last_name is a character column, use the following syntax (single quotes are required):
SELECT * FROM contact WHERE last_name = ‘#myvar#’
If address_id is a non-character column, use the following syntax:
SELECT * FROM contact WHERE address_id = #myvar#
How to Use a String Variable in a Comparison Expression
When comparing a string variable to a string value in an expression, always surround the variable name and the string value with single quotes, for example:
'#myvar#' = 'Dog'
About SQL Type Variables
SQL type variables are created in two ways:
- You can create user-defined SQL type variables using the Set Variable activity.
- A built-in SQL variable is created by the Loop Dataset activity, the Execute Script activity, and the Loop Connection activity.
If you ask just for the variable value of a SQL type variable, Toad returns the value from the first row and column of the result set. However, you can filter by column using this syntax:
<variable name>.<column name>.
For example, Region.Region_Name returns the first value (first row) in the column Region_Name.
Dataset/SQL Variable - Special Format for Filtering
You can use the following syntax format to filter data from the dataset variable (or any SQL type variable):
<dataset variable name>.<filter parameter>
Example: Loop_data_1_SQL.region_id, where region_id is one column in the dataset variable
For example, use this format in a SQL statement (in a Select to File activity) to select and export data based on each region_id value in the dataset variable. The statement is executed with each loop of the Dataset activity.
SELECT * FROM address WHERE address.region_id = :Loop_data_1_SQL.region_id
SELECT * FROM address WHERE address.region_id = #Loop_data_1_SQL.region_id#
Bind Variables
You can store global bind variable/value combinations in Toad
How to Create and Use Date Variables
In Automation scripts, a common use case is to create a variable to be used later in the script in a SQL statement (such as in the Select to File activity). When creating a variable for a date value, there are two variable types to choose from in the Set Variable activity: String or DateTime. The type you choose depends on how the value is to be used in the SQL.
- If the SQL expects a string value, use the String variable type.
- If the SQL expects a date/time value, use the DateTime variable type.
How to Define a Date Variable
| In the Set Variable Activity | ||
|---|---|---|
| Example SQL |
Variable Type |
Valid Values |
|
SELECT * FROM contact |
String | '2017-7-7' |
|
SELECT * FROM contact |
DateTime |
To_date('2017-07-07', 'YYYY-MM-DD') '2017-7-7' |
Using a String. For a String variable type, surround the date value in single quotes. This ensures that date delimiters, such as the dash (-) or forward slash (/), do not cause Toad to interpret the value as an expression.
‘2017-7-7’ is treated as a string
2017-7-7 is evaluated to 2003
Variable Scope
When you are building an Automation script that uses a variable, it is important to understand the variable's scope of visibility. This section describes the concept of variable scope in Automation scripts.
Terms and Definitions
| Term | Definition |
|---|---|
| Container |
Let's define a container as any one of the following:
|
| Levels |
Suppose that a script is composed of nested containers. Each container is a different level with respect to variable scope. Level 1—The outermost container is the script itself. The script is comprised of activities. The script and its activities in the main workflow comprise the first level. Level 2—Some activities can contain other activities. "Container activities" (such as the Loop Dataset) are at the second level. Level 2 is nested below level 1. Level 3—Container activities can contain branches. A branch within a container activity is the third level. Level 3 is nested below level 2. And so on, until we get to the innermost level. |
| Container Activity | A container activity is an activity that contains other activities. The Loop Dataset, Group, If Condition, While, and Parallel activities are container activities. |
| Set Variable Activity | Use this activity to define a variable. |
| Set Variable Value Activity | Use this activity to assign a new value to a previously-defined variable. |
Scope Rules
Basically, the scope of a variable includes all activities subsequent to the variable-defining activity AND either at or nested below the level at which the variable is defined.
The following are the rules for variable scope.
- Use a variable in an expression—You can use a previously-defined variable in an expression in an activity if the following are true:
- The expression activity is subsequent to the variable-defining activity
- AND the expression activity is at the same level or at a level nested below the variable-defining activity
-
Reassigning a variable value—You can reassign the value of a variable (except a SQL type variable) at any point within the variable's scope. This task is accomplished using the Set Variable Value activity. See Use System Automation Activities for more information.
Once a variable value is reassigned, the new value applies to activities subsequent to the original Set Variable activity AND either at the same level or nested below the level of the original Set Variable activity.
Example: You can use the Set Variable Value to turn a previously-defined variable into a counter (var=var+1) in the Loop Dataset activity.
-
Define a new variable with the same name—You can redefine a new variable with the same name, but only within an activity that is nested one or more levels below the variable-defining activity. For example, if you defined a variable in the main workflow of your script (first level), you can define a new variable with the same name within a container activity (second level). The scope of this variable value is restricted to that container activity.
You cannot define a new variable with the same name at the same level as the original.
Additional Examples to Illustrate Scope
- Run Automation Script activity—This activity is used in an Automation script (the parent script) in order to execute another Automation script (the child script). However, a variable defined in the parent script cannot be used in the child script.
- Loop Dataset activity—This activity is a container activity and can contain other activities, including a Set Variable activity. However, a variable defined in a Set Variable activity within the Loop Dataset cannot be used outside the Loop Dataset.
Execute/Schedule Automation Scripts
You can execute automation scripts on demand from the Automation window or the Project Manager. You can also schedule scripts to run on a recurring basis at a specific time. For example, schedule a script that performs long-running tasks to run when database activity is light.
When you schedule a script in Toad, the Job Manager creates a Windows scheduling task. Scheduled scripts run in batch mode. If using another scheduling application, specify "-batch=true" in the command. This executes the script in non-visual (batch) mode. See the procedure below.
To execute a script from the Automation window
-
Click Run in the Wizard bar. The script is compiled and executes immediately.
-
To view the script's execution log, select the Log tab.
-
If you want to schedule the script, first resolve any problems that caused activities to fail or disable failed activities. An activity can be disabled from its Activity Info window.
Tip: To execute a script from the Project Manager, right-click the script and select Execute.
To schedule an automation script
- With a script open in Automation, click Schedule in the Wizard bar. A Windows Scheduler task is created for the script and a task properties dialog opens.
- To schedule the script, select the Triggers tab. Select the scheduling trigger for this task and click Edit.
- In the Edit Trigger dialog, specify scheduling details. Click OK to save your changes and close the dialog.
- To run the script when you are not logged on, in the General tab select Run whether user is logged on or not. Then enter your Windows credentials when prompted.
- When finished, click OK to save your changes and close the task properties dialog. For detailed information about the task properties dialog, see
Note: To run a scheduled script against a password-protected database, the password must be saved in the Connection Properties dialog.
To execute a script in batch mode
-
To start Toad and execute a script in batch mode (non-visual mode), enter the following command and arguments:
<path to the toad.exe application> -batch=true “<path to Automation script>”
For example:
C:\Program Files\Quest Software\Toad Data Point 6.3\toad.exe -batch=true "<path\scriptfilename.tas>"
Notes:
- Automation scripts are compatible with the version of Toad in which they are created. To learn how to upgrade your scripts and migrate scheduled tasks that were created in a previous version of Toad,
-
The Automation module is disabled if Toad was installed with the Prohibit saving passwords option selected.
How to configure Toad to use Auth for sending SMTP Emails
To configure Toad to use SMTP Emails, we need to get the main data from the following third parties:
- Azure Portal where we register our application
- O365 Admin Center with at least one O365 License E5 where we register the O365 user
- Microsoft Exchange to configure our settings at a higher level
Configuration Steps:
- First, we register our application in Azure Portal | App Registration.
- In the O365 Admin Center, let’s go to create the user (toad in this case) that will use the application.
- In Exchange Admin Center | Settings, make sure the Mail Flow has the settings below at the organization level.
- At this point, Microsoft Documentation states that the application registered has to interface and use the SMTP server.
This refers to the setting from the MS Entra ID | Enterprise Application registration, NOT from App Registration. To do this it’s necessary to run these PowerShell commands in the order below:
NOTE: The OBJECT_ID is the Object ID from the Overview page of the Enterprise Application node (Azure Portal) for the application registration. It is not the Object ID from the Overview page of the App Registrations node. Using the incorrect Objec ID will cause an authentication failure.
New-ServicePrincipal -AppId d41b4835-027d-4efd-99be-82607a675c5c -ObjectId 779826e8-397c-4c74-b3a5-c874c82ed4af <-- This is the Object_ID from Enterprise ApplicationAdd-MailboxPermission -Identity "toad@questmail.onmicrosoft.com" -User d41b4835-027d-4efd-99be-82607a675c5c -AccessRights FullAccessGet-ServicePrincipal | flSet-CASMailbox -Identity toad@questmail.onmicrosoft.com -SmtpClientAuthenticationDisabled $falseGet-CASMailbox -Identity toad@questmail.onmicrosoft.com | Format-List SmtpClientAuthenticationDisabled - In Toad Data Point (this example), let’s set the email server specification (TenantID, ObjectID, and Secret_key).
