In Toad for Data Analysts 2.5 Automation Variables were added which greatly expanded what you could accomplish in an automation script. Variables can be used in almost every activity. This post lightly covers a variety of variable uses. Included at the end is a sample automation script that demonstrates using variables to customize a form letter and email to a list stored in a database. Sample files can be downloaded here. So let’s start from the top….
What is a variable?
A variable is a symbol or name that stands for a value. Variables play an important part in scripts. Rather than entering data directly into the script you can use variables. When the script is executed the variables are replaced with real values. In this way, variables can be used to represent and store data that may be different each time the task is run.
Using variables
To add a variable to an Automation script, click or drag the “Set Variable” system activity onto the design surface. Enter a name for the variable and initial value. It is not necessary to specify a data type for the variable. All variable values are interpreted to be a number or string based on the initial value given.
Variable Expression Builder
The variable activity contains an expression builder. Here you can build expressions using VB Script functions. Validate the expression using the ‘test’ button. The expression builder uses the initial value of the variable. The actual values are replaced at run time.
After a variable is defined and the initial value set, the variable values can be set through expressions or setting the value from a database column.
What can I do with an Automation Variable?
If Else Activity
The If Else Activity allows you to execute steps conditionally based on the value of a variable. To use, add an If..Else activity to your script and set the condition to execute the child activities. IE: If a > 0 Send Email.
While Activity
The while activity executes a set of tasks while a variable expression is true. The while condition needs to evaluate to false in order to terminate. This is usually done by setting a variable value. For example, the while condition is ‘a < 3’. The initial value of a is 1. The first step of the while loop is to set the value ‘a’ to a + 1. This is done by adding a Set Variable activity in the while loop and set the value with the expression a + 1. Execution of the while steps will continue until a is incremented to 3.
In the event that the while condition does not ever evaluate to true, there is a loop limit. The default value is 10. The while condition will not execute beyond the loop limit.
Find and Replace Activity
The find and replace activity opens a file and executes a find and replace as defined in the activity. The results can be written to a different location and filename.
Dereferencing Variables
The content of variables can be used by using the ‘#’ symbol before and after the variable name. Below shows the Find and Replace method using the contents of variable a. If a = 10 the replacement results will be ‘Department 10’.
Log Comment
Use the Log comment activity to dereference your variables through your script to debug.
Use File for Body of Email
Execute Script Result Variable
This one is my favorite use of an automation variable. The Execute Script variable includes a result variable. The variable contains the result set of the execution script. Use this activity with the Loop Dataset activity to set a variable value to the column value.
Loop Dataset Activity
The Loop Dataset activity executes the defined tasks on each row of the dataset. For each row you can set your automation variable to the value that row’s column value.
Putting it all together
That is a lot of choices of how to use automation variables. A simple example would be to read a list of email addresses from a database. For each email address customize a form letter and email. You can follow along with this example if you (Please visit the site to view this file). You will need to unzip the files to ‘C:Temp’ unless you want to change some paths.
1) Start Toad for Data Analysts and create a new Excel connection to the Names.xls file. (Use File | New Connection. Pick Excel from the group type and browse to the file. Press Select to connect.) I’m using Excel as my database for the email addresses. (Note: if you make your own Excel file you will need to put your data into a Named Region.)
2) Now let’s build the automation script. (You can open the sample Script_AutomationVarExample.tas or build one yourself following these steps.) Open the automation window and add three ‘Set Variable’ activities. We need a variable for the name of the person, their salutation, and email address. I named them NameVar, SalutationVar, and EmailVar. Set each initial value to ‘EMPTY’. Note, that variable names are case sensitive and you need to enter the word ‘EMPTY’ and not a blank.
3) Now add an Execute Script activity. You will need a sql file that will query the data in the Names.xls excel file. You can use the SelectNames.sql file or make one by using “Select * from names;” Since you are already connected to the Name Excel file a database connect ion activity will be added for you. Set the SQL Script attribute to your SQL file. When the script is run the result set will be stored in a result set variable. Give this variable a name by entering NAMES in the result variable attribute.
4) Now for each row in Names we want to perform some actions. To do this we need to add a Loop Dataset activity. When you click on the Loop Dataset activity from the toolbox it will add a Loop Data and Loop row activity. We need to point to the dataset we want to use. Double click on the Loop Data and choose the NAMES result variable from the dropdown.
5) For every row you will need to set your variables to the column value. To do this add three Set Variable activities. To set the value enter the name of the variable you are setting and dereference the result variable column. The syntax for this is ‘#’, Result Var name, period, Column name, ‘#’. For example the NameVar will be set to #NAMES.Name#. SalutationVar is set to #NAMES.Salutation# and EmailVar to #NAMES.Email#.
6) Now we want to use the row values we just set in the variables to customize a letter. To do this make a form letter and use as a template. In the letter we will place unique place holders. We will use the Find and Replace function to find the place holder and replace with the value of the variable. In the sample we have included our template called FormLetter.txt. We have place tokens for the salutation and name.
Dear %SAL% %NAME%
We always want to keep a copy of the template so the first Find and Replace activity saves the file as FormLetterR.txt. The Find and Replace activity only replaces one token at a time so we need to add a second one. This time we want to replace the Name token using the FormLetterR.txt.
7) Now let’s send this form to the user. Add an email activity to the loop. Make sure it is in the loop, if not you may need to drag it up into the loop. Dereference the EmailVar for the To: attribute. Remember to use the ‘#’ IE: #EmailVar#. Fill in the other email data, from, server, subject. At the bottom set the Use File for Body attribute to the path of your custom letter. C:TempFormLetterR.txt
8) Okay, you’re ready to roll. Just save and test. In the output you can follow the steps and see the variable values being set.
Start the discussion at forums.toadworld.com