Chat now with support
Chat with Support

Toad Data Point 5.7 - Installation Guide

Prepare to Publish Automation Scripts

Because the destination Intelligence Central (TIC) server may be slightly different than your local environment, an Automation script that runs successfully on your local machine may not always run successfully on a TIC server.

For example, file paths may be different, permissions to Networked files may be insufficient, the database client or DNS name might not exist. So, to ensure a successful execution, you may need to modify your script before publishing, or modify the Intelligence Central environment.

If you publish a script that needs modification, Toad identifies these items during the publishing process and displays a message describing the issue and the corrective action. Follow the instructions in the message.

For best results, review the following checklists to see if your script may need modifications prior to publishing.

Note: This feature is available in the Toad Data Point Professional edition only and requires access to a Toad Intelligence Central server.

Pre-Publishing Checklist - What You Should Do

Review the following checklists prior to publishing your script to help prepare it for execution in the Intelligence Central environment.

Install Toad Data Point on the Intelligence Central Server

To schedule and run Automation scripts on Intelligence Central, Toad Data Point must be installed on the Intelligence Central server. For more information about preparing the Intelligence Central server, see Prepare to Publish Data to Toad Intelligence Central.

SMTP Email Support on the Intelligence Central Server

Toad Data Point Automation supports only SMTP email transmission. To run Toad Data Point Automation scripts that send email, the Intelligence Central server must be configured to support SMTP email transmission.

Install Microsoft Excel on the Intelligence Central Server

To execute a scheduled Toad Data Point Automation script that includes an instruction to run a macro in an Excel spreadsheet, Microsoft Excel must be installed on the Toad Intelligence Central host computer.

Windows Domain

For best results, the Toad Intelligence Central host computer should be in the same Windows domain as the client computer used to publish the script.

Script Input and Output Files Checklist

For best results, move any input and output files (except those that Toad embeds) to a network location. In addition, make sure that the account under which Toad runs (Local System account by default) has the proper permissions to create or access those files. For more information, review the following.

Item (in script) Description
Input files: Stored locally

In general, do not publish a script that uses input files stored locally on your machine. There are a few exceptions: Toad files, linked SQL files, and Import/Export templates. These files are embedded into the script during the publishing process. See How Toad Manages Input and Output Files for a detailed list.

If your script uses input files like Excel or CSV, or input files for the Copy File, Delete File or Zip Files activities, move the files to a network location and modify the script accordingly before publishing.

Output files: Stored locally Do not publish a script that exports files to a local directory. Modify your script to export files to a network location before publishing.
Macro-enabled Excel files (.xlsm): Stored locally You cannot publish an Automation script that uses a macro-enabled Excel file (.xlsm) that is stored locally. If your script uses such a file, move the file to a network location and modify the script accordingly before publishing. For more information about macro-enabled files, see Using Macro-Enabled Excel Files.
Network location and permissions

If your script exports files to a network location, make sure that the export file location has the proper permissions in place to allow access by the account under which the script is run. By default, Toad runs and executes scripts on TIC under the Local System account. See Troubleshoot Scripts on Intelligence Central for more information.

This check is also necessary if your script uses input files stored in a network location.

Import/Export template: Non-SQL input and output files

If your script includes an Import/Export template that uses or creates input/output files such as Excel or CSV, these files must be in a network location. If your script includes such files and they are stored locally, move them to a network location and modify your script accordingly.

It is important to know that during publishing, even though the template and its linked SQL files are embedded, the non-SQL files used by the template are not embedded.

Variables in File Paths Checklist

Check the following before publishing.

Item Description
Variable in file path in script If your script contains a file path that includes a variable, you must replace the variable in the path with its value before publishing the script.

Connections Checklist

Check the following before publishing.

Item Description
Database Client on TIC server If your script contains an Oracle, Teradata, or Vertica connection, make sure that the corresponding database Client software is installed on the TIC server. See Prepare to Publish Data to Toad Intelligence Central for more information.
TNSnames.ora, DB2 catalog on TIC server Make sure that the correct entries exist in the client files that support the connection (e.g., Oracle TNSnames.ora, DB2 catalog).
ODBC DSN name on TIC server For ODBC connections, verify that the system DSN name also exists on the TIC server and that it matches the system DSN name you have defined locally.
Local Storage, local Excel, local Access connections in script

You cannot publish a script containing a connection to Local Storage, the Toad Sample database, a local Access database, or a local Excel file. If your script contains one of these local connection types, please modify or remove the connection before publishing.

Tip: You might consider, for example, moving the data source (Access database or Excel file) to a network location. Or you could publish the data as a Dataset to TIC first, and then modify your script accordingly.

Note: A network connection to an Access database or Excel file is allowed.

localhost connections You cannot publish a script containing a connection to a local database when the connection is defined using localhost. Modify the connection to use the IP address or the network name instead of localhost.
SQL Server connection using Windows Authentication in script

In general, Toad Intelligence Central does not support SQL Server connections that use Windows Authentication.

Special case: You can publish a script containing a SQL Server connection that uses Windows Authentication. But to run successfully, the following must be true:

  • The script must run under the default account.
  • The Windows Active Directory account used in the connection must be the same account under which the Intelligence Central App Server (Toad Application Server) runs.

Local Storage Snapshot Checklist

Check the following before publishing.

Item Description
Activity - Refresh snapshot in Local Storage You cannot publish a script containing an activity that refreshes a snapshot in Local Storage. Please address the activities that refresh a snapshot in Local Storage before publishing.

Locally Stored SQL Checklist

Check the following before publishing.

Item Description
Toad Views in script You cannot publish a script that includes a Toad View. Please replace the Toad View in the SQL statement or script with its corresponding SQL before publishing.
SQL Server Analysis Services saved Query in script SSAS Queries that you create and save are stored locally in Local Storage. You cannot publish a script containing a saved SSAS Query. (Saved Queries are displayed in the Object Explorer Queries tab for an SSAS connection.)

Other Considerations

FTP Connections

Item Please Note the Following
FTP connections using asymmetric encryption

If your script contains an FTP connection that uses asymmetric encryption (public/private key pairs), Toad transfers the client-side private-key file to the TIC database during publishing. In addition, Toad extracts this key from the TIC database, decrypts it, and stores it on the server during script execution on TIC.

Note: The decrypted key is stored on the server only temporarily—while the script is executing. Those with Administrator-type privileges to the server might have access to the decrypted key at this time.

 

  

Related Topics

Troubleshoot Scripts on Intelligence Central

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating