Chat now with support
Chat with Support

Toad Data Point 4.2 - User Guide

Automate Tasks

Use Automation to schedule a single script to run reports and queries, save results in Excel spreadsheets, and distribute results to the business community via email. You can schedule scripts that perform long-running tasks to run when database activity is light.  

You can also use variables in automation scripts. With variables, you can update information that may be different each time you run the script, such as the department name, revenue, fiscal quarter, or product name. See Using Variables in Automation for more information.

For example, assume you are an analyst at Nick's Flicks movie rentals. You need to run inventory and movie rental reports on a daily basis and distribute them to managers of the inventory department. Using Toad, you can create an automation script that runs the reports, exports them to Excel spreadsheets, and emails the spreadsheets to the inventory department everyday at 2:00 AM.

Troubleshooting: You must surround a variable name with the # sign (for example, #sleep#).

Note: Automation scripts are compatible with the version of Toad in which they are created.

To automate tasks

  1. Select Tools | Automation.
  2. Click Settings (icon) in the script design window and specify script settings. Enter settings in the bottom pane. Review the following for additional information:

    Activity Input Tab  

    Stop or continue on Error

    Select Stop On Error to stop the script if an error occurs.

    Send email when error occurs

    Select this option to send an email on script error.

    If selected, you must also click Compose Email to compose the email message and to edit the Email Server Settings.

    Note: When you specify the error email settings, Toad saves your settings and uses them in all subsequent automation scripts.

    Use relative path to find files

    Select to convert all local file paths to relative paths in all activities in the script (network file paths remain unchanged). This is useful if you want to share your automation scripts with colleagues or place them on a shared network.

    Toad makes the change the next time you run or save the script.

    If you deselect this option later, the script reverts back to the original absolute paths.

    Note: Toad deselects this option when publishing a script to Intelligence Central.

    Embed files into Automation script

    Embeds Toad input files into the script. This makes it easier to share the script with colleagues.

    Important: To use this option, deselect it, build your script, select input files, then re-select this option. This allows Toad to collect and save the input file paths.

    This option embeds Data Compare, Data Cleansing, Visualization, Pivot Grid, Toad Data Report, and Import/Export template files. Scripts used by the Run Automation Script activity are also embedded, as are SQL query files used as input by an Import/Export template.

    Important: Files linked using the Link to File option in the Select to File, Execute Script, and Select to Editor with Results activities are not embedded [.sql, Editor (.tef), and Query Builder (.tsm) files)].

    Toad embeds the files the next time you run or save the script.

    If necessary, at a later time you can deselect this option and then re-establish links to the original files.

    Note: Toad selects this option when publishing a script to Intelligence Central.

    Truncate log

    Select to overwrite the log file each time the script runs.

    Selected environment

    Select Test to run your script in test mode using the database connection and other settings specified under Test environment.

    Select Production to run your script in production mode using the database connection and other settings specified under Production environment.

    Note: The default connection is the currently active, open connection.

    • Root path variable name—Enter a variable name or use the default name. Then use this variable name in your script wherever you want to apply the path defined under Root path in the Test settings and the Production settings.
    Activity Info Tab  

    Name

    Enter a script name.
  3. To build a script, single-click an activity in the Toolbox, or drag an activity from the Toolbox to the Automation script design window. Repeat this process to add additional activities to your script.

    Note: You must drag the activity to plus sign, then release the mouse button after you see the activity's icon replace plus sign.

  4. Click an activity in your script workflow in the design window and then specify the activity's properties in the details pane (bottom pane). Repeat this process for each activity in your script. For a step by step tutorial on building an Automation script, see Getting Started with Automation.

    See the following topics for details about each activity and its properties:

    Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

    Note: Click Settings in the design window at any time to go back and review the Automation script settings.

  5. When you finish building your script, select one of the following actions from the Automation toolbar or the Wizard bar:  

    Add to Project

    Add an automation script to the current project in the Project Manager. The script is automatically placed in the project's Automation Scripts folder.

    Save

    Save an automation script (.tas) in a folder on a local or network drive.

    Run

    Run an automation script before scheduling it. The script executes once and the execution log is displayed in the Log window. Resolve any problems that cause activities to fail, or disable failed activities before scheduling the script.

    Tips:

    • Press F5 to run the automation script.
    • To view the script log, select the Log tab or select View | Output.
    Publish Publish script to Toad Intelligence Central.

    Schedule

    Schedule an automation script in the Job Manager. You can schedule a script to execute once or to run periodically at a defined interval. See Execute and Schedule Automation Scripts for more information.

Important: To rename an Automation script file, do so through Toad (File | Save File As), not through Windows Explorer. Renaming a script file using Toad ensures that log files are created correctly and properly synced with their scripts.

Tips:

  • Double-click an activity in the script design window to open the Activity Properties pane in a format used in earlier versions of Toad.
  • Right-click within the script design window and select Print Script to print the script's workflow diagram.

Automation Videos on Toad World

Use Database Automation Activities

You can use the following activities in automation scripts.

Note: When building an automation script, you must have the appropriate connections open for database activities.

Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

Activity Description

Select to File

Execute an existing SQL script, Query Builder file, or Editor file and export the results to an Excel, HTML, or CSV file.

  1. Select a database connection.
  2. Select a SQL file or enter a SQL statement.
    • Click the drop-down list to select a file from the currently open project in the Project Manager.
    • Click to browse to and select a SQL file.
    • Click to open the SQL file in the Editor where you can modify the SQL file.
    • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.

  3. RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
  4. Export File Type—Select an export file type.
  5. Export File Name—Click to select a file location and enter a file name. To use a variable as a suffix, enter the hash symbol before and after the variable name (#VariableName#).
  6. Suffix—Select a date or datetime suffix to append to the file name.

  7. Overwrite—Select to overwrite the file. If you did not add a suffix to the file name, you can deselect Overwrite to append a new worksheet to the Excel file with each script execution.
  8. Export Options—Click to specify more export options.

    • Worksheet name—Enter a worksheet name. For an existing file, select the worksheet from the list. Overwrite must be deselected to display the list.

      To add a worksheet to an existing file, select Append worksheet from the list and enter a new name.

    • Append rows—Select to append to the selected worksheet.
    • Start export at—Enter the column and row position at which to begin the export.

Tip: After exporting your file, you can use a subsequent activity in your script to email the report, to zip the report in a zip folder, or to copy the report to another location.

Execute Script

Execute an existing SQL script, Query Builder file, or Editor file. You can save the results of the script in a variable or export the results to Local Storage or an Intelligence Central dataset.

  1. Select a database connection.
  2. Select a SQL file or enter a SQL statement.
    • Click the drop-down list to select a file from the currently open project in the Project Manager.
    • Click to browse to and select a SQL file.
    • Click to open the SQL file in the Editor where you can modify the SQL file.
    • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.
  3. Save result set in variable—Enter a name for the result variable, which contains a data table with the results of the execution script.

    Tip: You can use the result variable with the Loop Dataset activity. See Use System Automation Activities for more information.

  4. Local Storage—To export the results to Local Storage, select a database and a new or existing table (or snapshot).
  5. Intelligence Central—To export the results to Intelligence Central as a dataset, click Publishing Wizard and specify your publishing options.

Review the following for additional information:

  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
  • Overwrite existing dataset—Select to overwrite an existing dataset with the same name.

Select to Editor with Results

Execute an existing SQL script or Query Builder query and save it and any results sets in a Toad editor file (.tef).

This file format is useful for building scripts that have large result sets that you do not want to continue executing or for saving results when you have not finished building a script.

  1. Select a database connection.
  2. Select a SQL file or enter a SQL statement.
    • Click the drop-down list to select a file from the currently open project in the Project Manager.
    • Click to browse to and select a SQL file.
    • Click to open the SQL file in the Editor where you can modify the SQL file.
    • Enter a SQL statement in the editor. You must first deselect the Link SQL file option.
  3. Result saved in Editor file—You can select an Editor file from an open project or browse to select a file. Click to edit the selected file.

    Tip: You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

  4. Append results—Select to include the result set in the Editor file.

Review the following for additional information:

  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Export Wizard

Export data from databases, tables, views, or a query into a CSV, Excel, or other file.

Review the following for additional information:

  • Export template—You can select an existing template, edit an existing template, or create a new template.

    Tip: You can use a variable in the file name to select an existing template file by entering the hash symbol before and after the variable name (#VariableName#).

  • Use Automation Connection—Select this option to use the connection associated with the Export Wizard activity. Clear the checkbox to use the connection associated with the export template.
  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Import Wizard

Import data from a CSV, Excel, or other file into a new table or append to an existing table. You can also import data from one database to another.

Review the following for additional information:

  • Import template—You can select an existing template, edit an existing template, or create a new template.

    Tip: You can use a variable in the file name to select an existing template file by entering the hash symbol before and after the variable name (#VariableName#).

  • Use Automation Connection—Select this option to use the connection associated with the Import Wizard activity. Clear the checkbox to use the connection associated with the import template.
  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Toad Report

Use this activity to open a Toad Data Report (executing the query) and then export the results to a file using the layout specified in the Toad Data Report. Several export file formats are available, including PDF, HTML, and Excel.

You can also select multiple Toad Data Reports and export the results to a single file.

Note: You must create a Toad Data Report file (.tdr) before you can select it using this activity.

Review the following for additional information:

  • Select a connection to execute the Toad Data Report query against.
  • Add Reports—Click to browse to and select one or more Toad Data Report files (.tdr). Click the arrow and select From Project Manager to add the reports in the currently-open project.
  • Edit Report—Click to edit the selected report.
  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
  • Export file—Enter a name and select a file type and location for your export file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Note: In most cases, the script uses the connection you specify in this activity. However, when a Toad Data Report contains a cross-connection query, the cross-database connection in the report takes precedence.

For more information about Toad Data Reports:

Toad Pivot Grid

Refresh an existing Toad Pivot Grid file and export results to an Excel, HTML, or CSV file. You can then add another activity to your script to zip the new file, attach it to an email, or copy it to a new location.

Note: You must create a Toad Pivot Grid file (.tpg) before you can select it using this activity.

Review the following for additional information:

  • Connection—Select a connection to execute the Toad Pivot Grid file against.
  • Pivot file—You can select a Toad Pivot Grid file from the currently-open project or browse to select a file. Click to edit the selected pivot file.
  • Export file—Enter a name and select a file type and location for your export file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
Profile Data

Automate data profiling tasks. This activity profiles data from a selected query and exports a report containing the profiling results.

  1. Select a database connection.
  2. Select a SQL file or enter a SQL statement.

    • Click the drop-down list to select a file from the currently open project in the Project Manager.
    • Click to browse to and select a SQL file.
    • Click to edit the selected file.
    • Enter a SQL statement in the editor.

  3. To specify options for the profiling task, click Profiling Options.
  4. Create Report—(Required) Select a location, select a file format, and enter a file name for the exported report (PDF, HTML, etc).
  5. Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

  6. Export Data Profiling File—(Optional) Select a location and enter a file name for the Toad Profiling Report file (.tpr).

    Note: You cannot attach this file in subsequent activities in the script.

See Data Profiling for more information.

Clean Data

Automate data transformation and cleansing tasks. This activity opens an existing Toad Data Cleansing file, performs the transform and cleansing tasks on the current result set, and exports the modified data to a file, Local Storage, or Intelligence Central.

  1. Connection—Select a database connection.
  2. Cleaning file—Select a Toad Data Cleansing file or edit an existing file using one of the following methods:
    • Click the drop-down list to select a file from the currently-open project in the Project Manager.
    • Click to browse to and select a file.
    • Click to edit the selected file.
  3. Error options—Click to specify error options.
  4. Select one or more of the following actions:
    • Export to a file—Select a file, or select a location and enter a new file name. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
    • Local Storage—To export to Local Storage, select or create a database. Then select or create a snapshot or table to receive the modified data.
    • Intelligence Central—To export the results to Intelligence Central, click Publishing Wizard and specify your publishing options.

Review the following for additional information:

  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.
  • Suffix—Select a date or datetime suffix to append to the file name.

See Transform and Cleanse Data for more information.

Visualize Data

Open an existing Toad Data Visualization file (.tdv), refresh the chart, and export contents as a report. Select from a number of output file formats, including PDF and Excel.

  • Data Visualization document—Select an existing Toad Data Visualization file.
  • Output file—Select a file location, file name, and file type for your output file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

See Visualize Data for more information.

Compare Data

Open a Toad Data Compare project (.dcp) and export the contents as a summary report and detailed object reports. Select from a number of output file formats for the summary report, including PDF and Excel.

  • Data Compare Project—Select an existing Toad Data Compare project file.
  • Summary Report—Select a file location, file name, and file type for the comparison summary report file.
  • Object reports folder—Select a destination folder for the detailed object comparison reports generated by the activity.
  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.
  • Generate sync script—Select a location and enter a file name to generate a synchronization script. Click to configure options for the synchronization script.
  • Execute script—Select to execute the generated script.
  • Compare result variable—Enter a variable name or use the default. This variable is assigned a value of True or False based on the results of the data comparison.

See Create Data Comparisons for more information.

Refresh a Snapshot

Use this activity to refresh a snapshot in Local Storage or in Toad Intelligence Central (TIC). This action will update the snapshot to reflect the current data in the source table.

Select one of the following:

  • Local Storage
    • Database—Select the database containing the snapshot to refresh.
    • Snapshot—Select the snapshot to refresh.
  • Intelligence Central
    • Select a destination—Select the TIC connection containing the snapshot to be refreshed.
    • Datasource—Select the data source in TIC containing the snapshot to refresh.
    • Snapshot—Select the snapshot to refresh.

Database Connection

In previous versions of Toad, the Database Connection activity was required in order to specify database connection details.

In the current version of Toad, you can specify the database connection details within each applicable activity, eliminating the need to use the Database activity. You may continue to use the Database activity if you prefer.

Note: The database connection specified for the Database Connection activity takes precedence over the connection specified for the individual activity.

Loop Connections

Loops through multiple connections and executes the activities you specify. Add as many activities as you want to the loop. The Loop Connections activity can have more than one branch.

  • Select connections to loop through—Click Add Connections to select one or more connections to loop through.
  • Connection description variable—Enter a new name for the variable or use the default name. The connection description variable is a SQL-type variable comprised of one row containing the connection information for the current connection.

    Use the following column names to gather connection information from the connection description variable. The exact content of each column depends on the provider type.

    Column Name Description
    DescriptionLong Description of the connection details.
    ProviderType Provider type such as SQLServer, Oracle, or SAP ASE.
    HostName Hostname or instance for the connection.
    UserName User name.
    TrueUserName If the connection supports and uses Windows authentication (e.g., SQL Server connection) this column contains the current Windows Logon name as: domain\username (e.g., "PROD\bgates"). Otherwise, if database authentication is used, it contains the same as the UserName column.
    Database Default Database context for the connection.
  • Next, add activities to the loop to execute for each connection in the connection list.

Note: The connections you specify in the Loop Connections activity take precedence over any connection specified in an individual activity.

Dimensional Viewer

Open an existing Dimensional View file, execute the query, and export the data to Excel, Local Storage, or Intelligence Central.

  1. Select an existing Dimensional View file.
  2. Export to file—To export data to Excel or .csv, select a file type, a file location, and a file name. Click Export Options to specify export options. In the Export dialog, each tab represents a view.

    • Worksheet name—Enter a worksheet name. For an existing file, select the worksheet from the list. Overwrite must be deselected to display the list.

      To add a worksheet to an existing file, select Append worksheet from the list and enter a new name.

    • Append rows—Select to append to the selected worksheet.
    • Start export at—Enter the column and row position at which to begin the export.
  3. Export to:
    • Local Storage—To export data to Local Storage, select a database.
    • Intelligence Central—To publish the data to Intelligence Central as a dataset, click Publishing Wizard and specify your publishing options.

See Dimensional Viewer for more information.

Notes: 

  • To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
  • The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
  • To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity.
  • Select the Log tab to review a script's execution log.
  • In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
  • Automation does not support positional parameters denoted by a question mark.
  • For each applicable activity, you can specify the database connection in the activity's properties. The default connection is the currently-active open connection.
  • The connection associated with an activity takes precedence over the connection information in a report or SQL script, unless the report or SQL script information is a cross-connection query.

Use File Automation Activities

You can use the following activities in automation scripts.

Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

Activity Description

Copy File

Copy or move one or more files to another location (for example, to a shared folder where they can be accessed by your business community).

Review the following for additional information:

  • Source file—Select the file to copy.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to copy. To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then modify the file name in the Source file field using wildcard characters and/or variables.
  • Copy to folder—Select the destination folder into which you want to copy or move the file or files.
  • Copy name—To rename the copied or moved file, enter a new name.

    Note: This field is only applicable when copying a single source file.

  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

  • Delete source file—Select to move the file from the source folder to the destination folder. Deselect to copy the file to the destination folder.

Delete File

Delete one or more files from one or more specified locations. You can also use this activity to delete files that are created previously in the script.

Files to delete—Click Add Files to select one or more files to delete. In the File Collection Editor, do one or both of the following:

  • To delete files created previously in the script, select a file in the left pane and click Add.

    Note: When a file name is displayed for selection in the left pane, any variable names that are used in the file name are visible (#MyVariable#), but suffixes are not visible.

  • To select other existing files, click to browse to and select the files.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to delete. To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then modify the file name in the Files to Delete pane using wildcard characters and/or variables.

    Note: As an example of using wildcards and variables, using the file name MonthlyReport_#var#*.xls, where #var# is Feb, would delete all reports whose file name begins with "MonthlyReport_Feb" in the same directory.

Log Comment

Writes a comment to the scripts's log.

Log message—Enter the text of the comment you want to insert into the log when this activity executes.

Zip/Unzip Files

Create a zip file (compressed folder) and add files to it. You can add files created previously in the script to the zip file. Use this activity to zip multiple files and then attach the zip file to an email using the Email activity.

You can also use this activity to unzip a zip file.

Review the following for additional information:

Zip Tab

  • Archive name—Select a location and name for the zip file. You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).
  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

  • Password—Enter a password if you want to password protect the zip file.
  • Hide password—Select this option to mask password during input.
  • Encryption—Select whether to create a zip file without encryption (Standard) or to encrypt the zip file.

    Note: If you encrypt the zip file, you must have a zip utility that can support 128-bit or 256-bit decryption.

  • Zip all files in this directory—Select a directory. All files in the directory will be added to the zip file. You can use both this field and the Source files field to select files to add to the zip file.
  • Source files—Click Add Files to select one or more files to add to the zip file. In the File Collection Editor, do one or both of the following:
    • To add files created previously in the script, select a file in the left pane and click Add.

      Note: When a file name is displayed for selection in the left pane, any variable names that are used in the file name are visible (#MyVariable#), but suffixes are not visible.

    • To select other existing files, click to browse to and select the files.

      In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to add. To use this method:

      1. Specify the file directory by browsing to and selecting one of the files.
      2. Then modify the file name in the Files to Archive pane using wildcard characters and/or variables.

Unzip Tab

  • Archive name—Select a zip file.
  • Password—If password-protected, enter the password.
  • Extract to folder—Select a location to extract the files to.

To learn how to use the Zip/Unzip Files activity, see the following:

Find and Replace

This activity opens one or more files and performs find and replace actions inside the files. You can write the results to a different location and file name.

Review the following for additional information:

  • Find/Replace—You can have multiple Find/Replace tasks within one activity. You can specify a different source file, save file, and action for each Find/Replace task.
  • Source file—Select the file in which to perform the find and replace action for the selected Find/Replace task.

    In this field, you can also use the asterisk (*) and question mark (?) wildcard characters in the file name to help you select one or more files (in the same directory). To use this method:

    1. Specify the file directory by browsing to and selecting one of the files.
    2. Then modify the file name in the Source file field using the wildcard characters.
  • Find—Enter the content to find.
  • Replace with—Select the type of replacement content: a value, contents from a file, or a variable value.
  • Set value—Enter the replacement value, select the replacement variable, or select a file containing replacement content.

  • Save file—Select a location and file name.

    The source file is selected by default.

  • Suffix—Select a suffix to append to the file name. Select from the list of existing suffixes, or click to select a variable or build a new suffix using the expression editor.

Note: Use drag and drop or up/down arrows to change the order of multiple Find/Replace tasks in one activity.

To learn how to use the Find and Replace activity, see the following:

FTP File

Upload or download one or more files using an FTP or SFTP connection. Review the following for additional information:

  • Select an FTP connection—Select an FTP connection or define a new connection.

    Note: You can define a new connection prior to using the FTP activity by selecting View | FTP Connections.

  • Transfer ONLY if—Select this option to set conditions for FTP transfer, and then select a condition from the list. If you select this option, the FTP activity only runs if the condition you specify is met.
    • Local directory exists—Select this option to transfer only if a local directory exists, then browse to and select the directory.
    • Local file exists—Select this option to transfer only if a local file exists, then enter the name of the local file.
    • Remote directory exists—Select this option to transfer only if a remote directory exists, then browse to and select the directory.
    • Remote file exists—Select this option to transfer only if a remote file exists, then enter the name of the remote file.
  • Define Operations—Click to specify the files and other options for the FTP upload or download task. Review the following for additional information:
    • Operation—Select Upload or Download.
    • Use file mask—Select this option if you want to select files by using a file mask. Then specify file mask filtering options in the File Mask fields at the bottom of the page.
    • Move files—Select this option to move the files from the source location to the target location instead of copying them.

To learn how to use the FTP activity, see the following:

Publish Files

Publish one or more files to Intelligence Central. You can select files created by previous activities in this script or other existing files.

Review the following for additional information:

Files to Publish—Click Manage Files to select files created previously in this script or to browse for other files to publish. In the File Collection Editor do one or both of the following:

  • Select one or more previously-created files in the left pane and click Add.
  • Click and then browse to select an existing file.

Publishing Options—Click Publishing Wizard to select an Intelligence Central connection and specify publishing options.

See Publish to Toad Intelligence Central for more information.

Notes: 

  • To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
  • The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
  • To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity.
  • Select the Log tab to review a script's execution log.
  • In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
  • Automation does not support positional parameters denoted by a question mark.

Tips:

  • For activities in which you can add a suffix to the output file, click to build a suffix using the expression editor.
  • You can use the expression editor to select a variable created in a previous activity or select one of your stored bind variables.

Use System Automation Activities

You can use the following activities in automation scripts.

Tip: You can save an activity and its settings as a template to reuse in other scripts. Right-click the activity in the design window and select Save As Template. After you create the first template, a Templates toolbox displays.

Activity Description

Set Variable

Automation variables are a powerful tool that can greatly extend your scripts by adding flexibility and customization. You can use variables to represent and store data that may be different each time the script executes. Variables can be used with almost every Automation activity.

Use this activity to define one or more variables.

Review the following for additional information:

  • Variable name—Enter a name for the variable. When you use the variable name later in the script, for example in a file name or in an expression, surround the variable name with the hash symbol (#MyVariable#).
  • Variable type—Specify a variable type.
    • SQL—Select SQL if you plan to use the result set of a query as the variable value.
    • DateTime—If you select DateTime variable type, you must use a date/time function.
  • Variable value—Enter a value or click to build an expression.
    • If you click to build an expression, select from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.
    • If you selected SQL as the variable type, the variable value options expand to allow you to specify a query. Select a database connection, then select a SQL file or enter a SQL statement. Toad uses the result set of the query as the value for your variable.

      See Using Variables in Automation for more information about SQL type variables.

  • Prompt during run—Select this option to instruct Toad to prompt for a new variable value during manual execution. The new value is used for the current execution only. The default value specified in Variable value remains unchanged. The default value is used for a scheduled script.

  • Add—Click to specify an additional variable/value pair. Use Up and Down buttons to reorder list.

To learn more about variables, see the following:

Set Variable Value

Use this activity to assign a new value to a variable previously defined in the current script or current activity. You can assign a new value to any variable type, except SQL.

Review the following for additional information:

  • Variable name—Select a previously-defined variable.
  • Variable type—Displays the variable type of the selected variable.
  • Variable value—Enter a new value or click to build an expression.
  • Add—Click to select an additional previously-defined variable.

See Variable Scope in Using Variables in Automation to learn more about how to use this activity with the Set Variable activity.

If..Condition..

Execute one or more activities if a condition you specify evaluates to true. You can add several branches to the If Condition activity to simultaneously evaluate more than one condition.

To learn how to use the If Condition activity, see the following tutorial:

Use the expression editor to build an expression that evaluates a condition. Typically this includes a variable (user-defined or built-in).

  • Expression—Build your expression by selecting from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

To learn more about variables, see the following:

Loop Dataset

Loops through each row in a dataset and performs the activities you specify. The dataset is the result set of a query you provide (the driving query) and is stored in the dataset variable. Add as many activities as you want to the loop. The Loop Dataset activity can have more than one branch.

Tip: You also have the option to select any SQL-type variable created in a previous activity to use as the dataset variable. Activities that create SQL-type variables include Execute Script, Set Variable, or Loop Dataset.

To learn how to use the Loop Dataset activity, see the following tutorial:

Review the following for additional information:

  • Dataset variable—Enter a new name for the dataset variable or use the default name.
  • Select dataset variable—(Optional) You can select one of the SQL-type variables created in a previous activity to use as the dataset variable.
  • Connection—Select a connection.
  • SQL script file—Select a SQL file to use as the driving query. After the script executes, the result set of the driving query is stored in the dataset variable.
    • Click the drop-down list to select a file from the currently open project in the Project Manager.
    • Click to browse to and select a SQL file.
    • Click to open the SQL file (you just selected) in the Editor where you can modify the SQL.

    Note: Click to preview the query results.

  • Link SQL file—Select to link the SQL script file you selected in the previous step.
  • SQL script—Enter a SQL statement to use as the driving query (if you did not select a SQL file).
  • RowCount variable—Enter a name for the row count variable or use the default. The value assigned to this variable equals the number of rows returned from the activity. See Using Variables in Automation for more information.

Tip: In the Loop Dataset, you can use the following format to filter data from the dataset variable when referencing it: <dataset variable name>.<filter parameter>.

Example: Loop_data_1_SQL.region_id, where region_id is a column name

While..

The While activity runs a loop while a condition you specify is true. The activity ends when the condition becomes false, or when the activity completes the selected number of loops. Each loop executes the activities you specify.

To learn how to use the While activity, see the following tutorial:

Use the expression editor to build an expression that evaluates a condition. Typically this includes a variable (user-defined or built-in).

  • Expression—Build your expression by selecting from the list of variables, operators, and functions. Click a category in the left pane to display a list of items in the right pane. Double-click an item to add it. Click Test to test the expression.
  • Loop—Enter the number of times to loop the activity. If the condition never becomes false, Toad ends the activity after this number of loops. The default is 10.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch. If you have multiple conditions, they do not have to be mutually exclusive. Multiple branches can be true.

To learn more about variables, see the following:

Send Email

Use this activity to distribute reports produced by previous activities in the script or to send notification of script execution status.

Note: To use this activity, an SMTP service must be running.

Review the following for additional information:

  • From—Enter your email address or click to auto-populate this field with your email address.

    Note: If you encounter a security alert message from your email application, and you want to use this auto-populate feature, grant Toad access (at least temporarily).

  • To—Enter an email address or click to auto-populate this field with your email address.
    • Prompt—Select to convert the To field to a variable and instruct Toad to prompt for a new variable value during manual execution. The new value is used for the current execution only. The default value specified in To remains unchanged. The default value is used for a scheduled script.

  • Email Server Settings—Click this button to specify your SMTP email server settings.

    • Server—Enter the address of your SMTP email server or click to auto-populate this field. If Toad is unable to auto-populate this field, contact your email administrator for the SMTP email server address.

      Note: The email server setting automatically defaults to that specified in the script's Settings (click the Settings icon and then click the Compose Email button).

    • User name / Password—If a non-SSL connection requires authentication, enter credentials. If using a secure connection, enter credentials, select Use Secure connection and select SSL or TLS.
  • Subject, Body—Enter a subject line and enter text for the body of your email.
  • Add Attachments—Click to add files created by this script or to browse for other files to add as attachments. In the File Collection Editor do one or both of the following:
    • Select one or more previously created files in the left pane and click Add.
    • Click and browse to select an existing file.

      In this field, you can also use the asterisk (*) and question mark (?) wildcard characters, as well as variables, in the file name to help you select one or more files (in the same directory) to add. To use this method:

      1. Specify the file directory by browsing to and selecting one of the files.
      2. Then, in the File Collection Editor, modify the selected file name (in the right pane) using wildcard characters and/or variables.
  • Use file for body—Select a file to use as the body of the email. This is useful if you want to include report contents in the body of the email instead of as an attached file. For example, in the Select to File activity you can select HTML as the output file format, and then use this HTML file as the body of the email.

    In another scenario, you can use a form letter as the body of your email. Create a form letter template and then customize the file with the Find and Replace activity.

Note: In the To, CC, Subject, and Body fields: You can use a variable in this field by entering the hash symbol before and after the variable name (#VariableName#).

To learn more about how to use email in an Automation script, see the following:

Run Program

Run a program with optional command-line arguments. Review the following for additional information:

  • Program—Select a batch file (.bat) or executable (.exe) file.
  • Arguments—Enter command-line arguments. You can also enter a variable, such as #sleep# in this field.

    Note: If you want to run a macro in Access, enter databasename /x macroname in this field. For example, if you have a macro that beeps and runs in your Northwind database, you would enter Northwind 2007.accdb /x beep.

  • Run directory—Select the directory to change to, when running the program.
  • Run style—Select the window state for the program when it runs.
  • Verb—Enter verbs used when running the program, such as "Print".
  • Wait for program to exit—Select this option to pause if there is an error. Select duration of wait time in minutes.
  • Return code variable—Enter a variable name or use the default name. You can use this variable with the If Condition activity.

    Return code (exit code) values of 16384 (4000 hex) or larger do not generate a script error in this activity. Other return code values do. This allows you to use your program to generate a custom return code value in this range and use it in subsequent activities without generating a script error.

Note: If the script opens a command-line window, you must manually close the window once the script completes.

Run Automation Script

Run another automation script in your current automation script.

Review the following for additional information:

  • Automation script—Select an automation script (.tas file).
    • After selecting a script, click to open the child script in a new window.
  • Return code variable—Enter a variable name or use the default name. After the child script executes, a return code value is stored in this variable. Return code values are:
    • 0 = Successful execution
    • 999 = An error occurred during execution
    • 111 = An error occurred, but you selected continue on error.

    You can use this variable with the If Condition activity.

Pause

Stops an activity for a specified number of seconds before continuing. This is useful, for example, if you are using parallel activities and need to pause one branch while waiting for results or the complete execution of another branch.

For an example of how to use the Pause activity in an Automation script, see the following:

Group Activities Use this activity to group and organize two or more activities together.
Throw Error

Use this activity to generate an error and add a custom error message to the script execution log.

Select Stop On Error to stop the script after the error is logged. This option is independent of the global Stop/Continue on Error option specified in script Settings or the Stop on Error option in the Activity Info tab. This activity generates a script exit code of 555.

Parallel

Run two or more activities in parallel.

After adding the activity to the design window, hover over the activity to display a drop-down list of view options.

View Parallel—Normal view.

View Cancel Handler—Select to add cancel handler activity.

View Fault Handlers—Select to add fault handler activity.

This activity can have one or more branches. To delete a branch, select it and press Delete. To add a branch, right-click the activity (the outside loop) and select Add Branch.

Notes: 

  • To disable an activity, right-click the activity in the design window and select Disabled. This is helpful if you want to disable an activity while testing other activities.
  • The Stop on Error option for each activity allows you to select whether to stop or continue execution if Toad encounters an error during that activity.
  • To prevent an error in one activity from causing a failure at the script level, clear the Generate Error check box (Activity Info tab) for that activity.
  • Select the Log tab to review a script's execution log.
  • In the Suffix field, the drop-down list includes two predefined suffixes (Date and DateTime). The list also includes any suffixes you have created and saved since installing Toad. To delete a suffix from the list, select the suffix, click , delete the suffix in the Expression field, and click OK.
  • Automation does not support positional parameters denoted by a question mark.

 

Related Documents