Hi my name is Debbie Peabody and I’m beginning a Blog series on Automation. I have talked a lot about Automation but there are still many examples of real world scenarios that might help you out with your daily tasks.
In this Blog series I will present the data in both video and written format. In this post I will start with the basics of how to get started with Automation. (If you have been creating and running automation scripts for a while, stay tuned, in our next blogs we will have some new items just for you).
Toad Data Point provides an automation utility to easily script database activities and schedule them using the windows scheduler. In this way database tasks can be automated to save time.
Building a Script
When building a query in the query builder or editor you can export the results to excel by selecting the Send to Report wizard button. After you format this data the wizard helps you automate this process by sending to the automation window.
In Toad Data Point 3.3 we have changed this wizard bar action to invoke an Automation Tutorial. This tutorial guides your through the 4 most common types of Automation scripts. It teaches you what elements are needed for that type of script and helps you build the script. There will be a video and Blog on this tutorial as part of this series as well as with the release of TDP 3.3.
The Automation Designer
This is the toad Automation script designer. A basic automation script consists of settings, a database activity and generally an email notification, used to confirm success and distribute reports.
The Automation window is available from the wizard bar as shown, the tools menu or launch window. Each script is built using the tool box activities and script settings. To enter details enter values in the area at the bottom of the designer. The properties will change for each focused activity.
The script activity contains values for error processing, logging level and changing connections from test to production.
Let’s build a new script. To add an activity, drag or single click one of the activities from the left. Activities that need a database connection will be added with the current connection as the default.
To change, select the drop down and change the connection. A password must be saved for automation scripts to run unattended.
The red explanation point is an indication that more information is required to complete the activity. Click on the explanation symbol to see a list of properties that are required. Most activities require an input and an output. Press the Esc button to close the explanation hint.
For example we are using the select to file activity. This requires SQL as an input. You can browse to select a QueryBuilder or SQL file. Or by unchecking the link sql file box, you can enter the SQL manually.
The output file can be defined by navigating to the destination path and providing the file name. The export type of Excel, Html, or Csv, can be selected as the file type. A dynamic file name can be created by supplying a file suffix. A date or Date Time format is supplied.
You may find that once you start building scripts there are many things that can be automated. Some activities you may use in every script. These activities can be saved as templates and reused in other automation scripts. Just right click on an activity and provide a name for the template. These are saved in the tool box in the template section. Click to use in any of your automation scripts.
When you are done building your script press the Run button. You will be prompted to name and save your script. The script is then validated and compiled. If your script validates and compiles successfully the script will run. The progress of the script is written to the output window. A timestamp and ‘done’ is entered to let you know when the script is complete.
If more details are needed you can change the logging level to Verbose. To do this, click on the script settings. The level as well as the location can be changed here. Exception handled can be enabled and an email sent to you when there is an error. The email can attach the log file. Press the compose email button to enter Email details.
While exception handling is not turned on by default, it is highly suggested that you set up and include this in every script. Email on success as well as failure helps you to be confident that your processes are occurring as you planned. You can also set the behavior of stopping or continuing on error. Continue on error can be set at the script level or activity level. To set at the activity level go to the activity info tab of that activity. Here you can also enable or disable the activity. If you want to run your script without that activity but do not want to delete (for debug purposes) you can use the enable/disable activity option.
Once you have tested your script you can schedule it by sending to the job management window. The Job management window uses the local windows scheduler. The initial task is filled in for you. Simply provide your password by selecting the Set Password button. On the schedule tab provide the start time and frequency. Click Okay and save the task.
You might want to test your scripts on a test database and then for deployment change to a production server. Using the setting you designate these connections and it will change all connections in the activities when going between test and production.
When the task is run, toad executes the script. You will only see a toad icon in the lower right hand section of your windows task bar. The status of the execute task will display in the job management window as well as in your windows task manager.
Toad Data Point Automation can increase your productivity by handling your repetitive report and deployment needs.
How to get the most out of Toad Data Point
Toad Data Point, is a powerful tool that will help you access and prepare data for faster business insights. Toad Data Point enables business or data analysts to seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data sources with near-zero transition times. Users can connect, query and prepare data for faster business insights.
Whether you are currently a Toad Data Point customer or just getting started with our free 30-day trial, learn more and access the Toad Data Point User Guide.
If you’re in a trial and Toad Data Point is helping you access and prepare data for faster business insights, buy it now or contact a sales representative.
Useful resources
Video: Top 5 reasons to buy Toad Data Point Professional Edition, a solution for simplifying data access, integration, and provisioning.
Case study #1: Dell: Enterprise financial group solves data prep challenge.
Case study #2: Opening doors and creating opportunities with data insights.
Related blogs
Access more Toad Data Point blogs.
Got questions?
If you have any questions, please post questions to the Toad Data Point forum on Toad World.
Start the discussion at forums.toadworld.com