Automation and Executing Scripts
Summary
In this tutorial, you will build a script that uses the Execute Script activity to run a small SQL script prior to executing other database activities.
In this tutorial you will learn:
- How to use the Execute Script activity in Automation
- How to perform a database setup task using the Execute Script activity
- How to configure the Execute Script activity to continue in the event of an error
You will need:
- A sample database
- A SQL script that drops and recreates the sample database
-
An Import Wizard template (configured to import a table into the sample database)
Note: For a tutorial on how to create an Import Wizard template, see Automation and Importing Data.
Introduction
The most common Automation tasks involve importing and exporting data. You may find, however, that sometimes these tasks also require some database setup. Database setup can be accomplished by adding the Execute Script activity to your Automation script. The Execute Script activity can be used to execute simple or complex scripts.
In this example, you will use the Execute Script activity to "clean up" a database by dropping and then recreating it prior to importing data.
For this tutorial, you will want to use a sample database or a sandbox area.
Build Script
- Select Tools | Automation to open a new Automation window.
- Click the Execute Script activity to add it to the Automation design window.
- In the Activity Input tab, select the connection for a sample database.
- Then select the SQL file that drops and recreates the sample database (or enter the SQL script into the editor box).
- Now click the Import Wizard activity in the toolbox to add it to the design window. Make sure the activity is added to the Automation workflow below the Execute Script activity.
-
In the Activity Input tab, click
to browse to and select your Import Wizard template to import data into the sample database.
Note: To learn how to use the Import Wizard activity, see the Automation and Importing Data tutorial.
-
Click
- After the script executes, the Log tab displays in the details pane. You can review the log to see how the script executed your drop and recreate SQL and then imported data using your Import Wizard template.
Specify to Continue on Error
If you have a situation where the SQL script in your Execute Script activity errors, but you would like the Automation script to continue, you can configure the script to continue after an error. For example, if the database doesn't exist, the script will error, but you would want the script to continue in this situation.
- To demonstrate this situation, in the Object Explorer, manually delete your sample database (right-click the database and select Operations | Drop Database).
- Now, run your Automation script. The script fails because your SQL script instructs Toad to drop the database that doesn't exist. But in this case, we are not concerned with this error, because the SQL script eventually recreates the database.
- To instruct the Automation script to continue, even if the SQL script errors, click on the Execute Script activity in the Automation design window.
-
Select the Activity Info tab for the Execute Script activity, and deselect the Stop on Error option. This instructs the Automation script to continue even if this activity (and only this activity) errors.
The Execute Script activity can be used to execute simple to complex scripts. It is a simple activity, but can be useful in many ways.
Schedule A Script
See Schedule Your Script to learn how to schedule the Automation script.