Toad Data Point 6.1 - User Guide

Automate

Getting Started with Automation

Summary

This tutorial helps you get started with Automation by giving you an overview of the Automation module and then guiding you through the process of creating a basic script.

In this tutorial you will learn:

• How to use the Automation window
• How to create a basic script using the Select to File activity
• How to test and run a script
• How to schedule a script
• How to create a template for reusing an Automation activity

Watch a video version of this tutorial: Getting Started with Automation.

Note:  The Automation module is disabled if Toad was installed with the Prohibit saving passwords option selected.

Introduction

Toad provides an Automation utility that allows you to easily script database activities and schedule them using the windows scheduler. In this way, database tasks can be automated to save you time. Toad Automation can increase your productivity by allowing you to automate tasks that you perform repeatedly.

The Benefits of Using Automation

• Automating repeatable tasks allows you to be more productive and increases your efficiency.
• Toad Automation scripts are very flexible and extensible, allowing you to customized them to your needs.
• You can create and use variables in Automation scripts. Variables allow you to make your scripts flexible and to customize your scripts to different audience and company needs.
• You can incorporate a variety of templates into Automation scripts. Using templates in your scripts can help you get the most out of automating your tasks. Templates include: Microsoft Excel^® files, Export Wizard templates, or Toad Report templates. You can even create an Automation activity template allowing you to reuse an activity.
• There is no limit to the number of tasks you can include in an automation script.
• Toad Automation scripts are conveniently scheduled and run by the Windows scheduler. You can track and manage your scheduled scripts using Toad or the Windows Scheduler interface.

The Automation Window

1. To get started with Automation, open an Automation window by selecting Tools | Automation.

   The Automation window consists of a Toolbox pane (along the left side of the window), a design pane (top half of the window), and a details pane (bottom half of the window).

   

   Toolbox—Stores the Automation activities. Click an activity in the Toolbox to add it to the script design pane. Activities are listed by category: Database, File, or System.

   Script Design Pane—Graphically displays the script as a workflow. Use the script design pane to build an Automation script. Drag an activity to the design pane to add the activity to a script.

   Note: You must drag the activity to anAdd Activity icon in the Automation workflow. When you see the activity's icon displayed in the workflow, release the mouse button.

   Details Pane—Displays an activity’s settings. Click an activity in the design pane to display its configurable settings in the details pane. Use the details pane to specify the settings for each activity in the script.

2. In the Toolbox, find the Database Activities group. Single-click the Select to File activity to add it to the script design pane.

3. With the Select to File activity still selected in the script design pane, view the details pane. The details pane allows you to configure settings for the selected activity. The input fields change depending on which activity is currently selected in the design pane.

   Note: The Activity Input tab is the tab where you will specify most of an activity's settings.

4. Click next to the Select to File activity. Toad uses this icon to indicate that more information is required to complete the activity. Click or hover over the icon to display the missing information. Use this icon to help you determine which input fields are required.

   Note: This icon is also used to indicate a validation error, for example, when referencing a variable that has not been created yet.

5. Now click the Settings icon in the design pane. The details pane changes to display input fields for the script’s settings. You can use this area to specify script error handling, logging level, and run environment settings.

   

6. Click the Stop on Error button, if not already selected. This instructs the script to stop if an error is encountered.

   Note: You can also enable or disable the Stop on Error option for each individual activity. If you know that a particular activity will have an error, you can disable the Stop on Error option for just that activity. This option is located on the Activity Info tab for each activity.

7. In addition to selecting the Stop on Error option, enabling the Send email when error occurs and Attach log file to email options is also preferred. This can be very useful if your script encounters an error. Enabling your script to send an email notification on error, as well as on success, helps you to feel confident that your processes are running as expected.

   If you choose to enable these options, you must specify email settings. To specify email settings, click the Compose Email button.

8. Keep this Automation script window open, and proceed to the next section.

Create a Basic Script

1. If you kept the previous Automation window open, you will have a basic script consisting of one activity, the Select to File activity. Click on the Select to File activity in the design pane.

2. In the Activity Input tab, in the first text box, select a database connection, or use the default. The default database connection for each database activity is always the currently-active open connection. To select a different database connection, click and browse to a different connection.

   Note: If you schedule a script to run against a password-protected database for which you did not save the password in Toad, the script will error. To save the password, right-click the connection in the Connection/Navigation Manager and select Properties. Enter the password and select the Save Password option.

   Tip: For Snowflake connections, it is considered best practice to use only the Snowflake user account authentication when creating Automation scripts to avoid unnecessary workflow interruptions.

3. Now select an input. Most activities require an input, and in the Select to File activity the input is a SQL statement. You can input a SQL statement using one of the following methods:
   • Manually enter a SQL statement in the editor box (deselect the Link SQL file option).
   • Click to browse to and select a SQL file.
   • Select Link SQL file, then click to browse to and select a SQL file. This option retains a link to the SQL file.
   • Click to open the SQL file (you previously selected) in the Editor where you can modify the SQL.
   • Select Link SQL file, then click the drop-down list to select a file from the currently open project in the Project Manager.

   

4. After selecting or entering your SQL statement, select an output. Select Excel in the Type field in the Export File section.
5. Click in the Name field and select a location and enter a name for your new Excel file.
6. You can also add a suffix to the file name. In the suffix field, click the drop-down to select a datetime stamp, or click to build an expression. You can use this method to add a variable or a custom datetime stamp. Appending a datetime stamp to the file name allows you to create a unique file with each script execution.

7. You now have a complete activity, with input, output, and database connection specifications. You can save and reuse this activity in other scripts by saving the activity as a template. To do this, right-click the activity (after configuring all the settings) and select Save As Template. Enter a name for your template and click OK to save it.

   Your template now appears in the Toolbox in the Templates category.

   

8. Keep the Automation script window open and proceed to the next section.

Test and Run Your Script

1. To test your script, click Run in the Wizard bar.
2. Toad will prompt you to save your script (if you have not already done so). In the Save As dialog, select a location, enter a file name, and click Save.
3. Toad then validates your entries and compiles the script. If these processes are successful, the script runs.

4. While the script is running, Toad displays your script’s execution progress in the Log tab. The message “Done” indicates script execution is finished.

   

5. When a script produces an output file, a hyperlink to the output file is included in the Log. If your script ran successfully, click the output file hyperlink to view the new Excel file created by your script.
6. Click the Settings icon to return to the script’s settings.

7. If you require a more detailed Log, you can change the logging level. In the Settings Activity Input tab, select Verbose from the Logging level drop-down list to create a more detailed log.

   Note: If you want to change the location of the Log file, select a new location in the Logging folder input field.

8. Toad allows you to specify both a Test database connection and a Production database connection for each script. Toad also allows you to specify which connection to run your script against when the script executes. Both of these settings are made in the script’s settings. This feature allows you to easily test your script in a test environment before running your script in the production environment.

   To specify a test and a production database, make the selections in the Connection text box under Test environment and Production environment in the Settings Activity Input tab. To select which of these databases to run your script against, select Test or Production from the Select environment drop-down list.

9. Click the Select to File activity to return to its settings. Then select the Activity Info tab.

10. If you remember from the overview of the Automation window, Toad allows you to enable or disable each activity in a script. This can be helpful when building a script, as it allows you to test one particular activity at a time by disabling the other activities.

    In the Activity Info tab, click Disabled to disable the Select to File activity. Notice that the activity now appears shaded in the design pane. To enable the activity again, click Enabled, or right-click the activity in the design pane and select Enabled.

Schedule Your Script

Once you have thoroughly tested your script, you can schedule it.

1. To schedule your script, click Schedule in the Wizard bar.

2. The Job Manager window opens and a task properties dialog displays. The task properties dialog is pre-populated with the information required to run your script as a scheduled task. You only need to schedule it. (The Job Manager uses the Windows Scheduler.)

   

3. To schedule the script, select the Triggers tab. Then select the scheduling trigger and click Edit. In the Edit Trigger dialog, ensure that the On a schedule trigger type is selected. Then specify a frequency, start time, interval, etc.
4. Click OK to save your schedule. Click OK to save and close the task properties dialog.
5. Your task (script) is now listed in the Task List in the Job Manager. Double-click your task in the list to open the task properties dialog again where you can make modifications, such as re-scheduling or changing the script location. Use the Task List to review the status of your tasks (scheduled scripts).
6. By default, the scheduled task runs under your Windows account. If you change your Windows password, you may also need to update the password in your scheduled task (depending on your version of Windows). Click and select the task to update. Click OK and then enter your password when prompted.
7. After upgrading to a new version of Toad, you must migrate the scheduling tasks to the newer version. See Upgrade Automation Scripts and Scheduling Tasks for more information.
8. Script Execution and Status. When Toad is in the process of executing a scheduled script, you will see the Toad icon in the lower right corner of the Windows taskbar. To check the status of your scheduled scripts, open the Job Manager (Tools | Administer | Job Manager) and select the Task List.

Now you have learned how to increase your productivity by using Toad Automation to handle your repetitive reporting and deployment tasks!

Learn More

Other Toad Automation tutorials are available. To see more examples and to learn how to build other types of scripts to automate everyday tasks, start with the following:

• Automate Exporting to Excel Reports
• Automate Complex Excel® Reports
• Automate Exporting Data
• Automation and Importing Data
• Automate Sending Email
• Automation and Variables

Tutorial: Automate Excel Reports

Summary

This tutorial walks you through the process of automating the task of exporting query results to simple Excel reports. You will create three different, and very useful, simple Excel reports.

In this tutorial you will learn:

• How to use Automation to export query results to Excel
• How to use the Select to File activity in Automation
• How to automate an Excel report that appends a new worksheet with each execution
• How to automate an Excel report that uses multiple queries to produce multiple worksheets

Watch a video version of this tutorial: Automate Exporting Data to Excel.

Introduction

After creating a result set in Toad, you may need to send the results to your end users. The easiest way to create a report containing query results is to export the data to an Excel file by right-clicking the data grid and selecting Quick Export | File | Excel File. This action creates a basic Excel spreadsheet containing the result set from the data grid.

If you want to create more than a basic spreadsheet and automate the process, Toad Automation helps you build a variety of Excel reports, from simple worksheets to complex and attractive Excel reports.

In this introduction to automating Excel reports, you will learn how to automate the process of exporting data to a variety of simple Excel reports.

Note: To learn how to get started with Automation, see Getting Started with Automation.

Create a Simple Excel Report

1. Select Tools | Automation.

2. Click the Select to File activity in the Database Activities pane of the Toolbox, or drag the activity from the Toolbox to the Automation design window.

   

3. When the Select to File activity is selected in the design window, the Activity Input tab displays. Select a database connection in the first text box or use the default (the default is the currently-active open connection).
4. Then select a SQL file or enter a SQL statement using one of the following methods:
   • Manually enter a SQL statement in the editor (deselect the Link SQL file option).
   • Click to browse to and select a SQL file.
   • Select Link SQL file, then click to browse to and select a SQL file.
   • Click to open the SQL file (you previously selected) in an editor where you can modify the SQL.
   • Select Link SQL file, then click the drop-down list to select a file from the currently open project in the Project Manager.
5. Select Excel in the Type field in the Export to File section.

6. Click in the Name field to select a location and enter a name for your new Excel file, or you can select an existing file.

   Note: To specify more-detailed options for the Excel file, click the Export options button. See Specify Excel Export Options.

7. (Optional) You can choose to create a unique file by adding a suffix. In the suffix field, click the drop-down to select a datetime stamp, or click to build an expression.

8. Select Overwrite if you want to overwrite the file.

   Note: If you did not add a suffix, you can deselect Overwrite to append a new worksheet to the Excel file with each script execution.

9. To save your Automation script, click Save in the Wizard bar. Then select a location and file name for your script.
10. To test your script, click Run in the Wizard bar.
11. After the script executes, the Log tab displays. Click the hyperlink in the Log to view the Excel file.
12. To learn how to schedule your Automation script, see Schedule Your Script.

That was simple, wasn't it. Now move on to the next example to learn how to create a slightly more complex Excel report.

Create a Multi-Page Excel Report Using One Query

Suppose you want to create a new report each week from the same query while retaining the previous week’s results. In this case, you want to append each week’s results as a new worksheet to a single Excel file. This is an excellent option if you want to compare the results each week (or each day, month, etc.) to see how the data is changing. (And Toad has several data compare features in the application to let you do that.)

1. Select Tools | Automation to open a new Automation window.
2. Click the Select to File activity in the Toolbox to add it to the Automation design window.
3. Select a database connection.
4. Select a SQL file or enter a SQL statement using one of the methods described in the first example.
5. Select Excel in the Type field in the Export to File section.
6. In the Name field, click to select a location and enter a name for your file.
7. Leave the suffix field blank (adding a date/time suffix could create a new file with each script execution and this is not the objective of this example).
8. Deselect Overwrite (you do not want to overwrite the file).
9. Now click Export options. The Export options dialog opens.
10. In the Worksheet name field, enter a name to be used as the base worksheet name.

11. Select Append timestamp to named worksheet. This will append the date and time (that the data was exported) to the base worksheet name. Click OK to save your settings and close the dialog.

    

12. Click Run to save and run your script.

13. Open the Excel file by clicking the link in the Log tab. Notice that the new worksheet’s name includes the base name you specified suffixed with the timestamp.
14. Run the script a second time and open the file. A new worksheet with a new timestamp was added to the Excel file.

The output of this script is one Excel file containing multiple worksheets. Each worksheet contains a result set from one execution of the script. And each worksheet name records the date and time the result set was exported.

Create a Multi-Page Excel Report Using Multiple Queries

Another variation of the multi-page Excel report is to export the results of multiple queries. Each worksheet in the Excel file will be populated with the result set of a different query. This can be accomplished by using a SQL file that contains multiple SQL statements as the input file.

1. To start, open a new Automation window.
2. Click the Select to File activity to add it to the design window.
3. Select a connection.
4. Select a SQL file or enter a SQL statement using one of the methods described in the first example. However, this time use a file that contains multiple SQL statements (or enter multiple statements in the editor).
5. Select Excel in the Type field in the Export to File section.
6. Click in the Name field to select a location and enter a name for your new Excel file.

7. Click Export options and make sure the Worksheet name field is blank.

   Note: If the Worksheet name field is not blank, your script will export both result sets into the named sheet, and the second result set will overwrite the first.

8. Selecting to overwrite the existing file or to add a date/time suffix to the file name is optional in this example.

9. Click Run to save and run your script.

10. Open the new file by clicking the link in the Log tab. Notice that the file contains a separate worksheet for the result set of each query.

The output of this script is one Excel file containing multiple worksheets. Each worksheet contains the result set of a different query.

Note: To learn how to export the result sets of multiple queries into a single worksheet (using Automation), see Automate Complex Excel® Reports.

Learn More

As you can see, with Toad Automation you can easily create basic Excel reports, but with a few more script configurations and the use of templates, you can also create attractive and more complex reports. To learn how, see the following tutorials on Automating Excel Reports.

• Automate Complex Excel® Reports
• Automate Excel® Pivot Table Reports
• Automate Excel® Macro-Enabled Reports

Schedule A Script

See Schedule Your Script to learn how to schedule the Automation script.

Related Topics

Getting Started with Automation

Use Database Automation Activities

Specify Excel Export Options

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#).

Notes:

• Automation scripts are compatible with the version of Toad in which they are created. To learn how to upgrade your scripts and scheduled tasks that were created in a previous version of Toad, see Upgrade Automation Scripts and Scheduling Tasks.
• The Automation module is disabled if Toad was installed with the Prohibit saving passwords option selected.

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	Use this area to specify a "test" database connection and a "production" database connection and then easily switch between the two. Test—Select to run your script in test mode using the database connection and other settings you specified under Test environment. Production—Select to run your script in production mode using the database connection and other settings you specified under Production environment. Note: The Connection field under both environments defaults to the currently-active open connection. Variable names Root path—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 either the Test settings or the Production settings. The path that is applied depends on which environment (Test or Production) is selected at the time the script is executed. Run mode—Enter a variable name or use the default name. The value assigned to this variable is either Test or Production, based on the environment selected at the time the script is executed.
   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 , then release the mouse button after you see the activity's icon replace .

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:

   • Use Database Automation Activities
   • Use File Automation Activities
   • Use System Automation 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.

   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. See Publish Automation Scripts to Intelligence Central for more information.
   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.

Related Topics

Execute and Schedule Automation Scripts

Using Bind Variables