Toad for SQL Server 8.1 - Installation Guide

Automate FTP Upload and Download

Summary

This tutorial demonstrates how to automate the process of downloading or uploading using Toad's FTP utility.

In this tutorial you will learn:

• How to use the FTP activity in Automation

You will need:

• A few files with the same file type (e.g., .xlsx) stored on a remote FTP site
• An empty text file (Ready_to_Download.txt) to use as a signal file

Introduction

Toad conveniently includes an FTP utility (View | FTP Connections). In addition to using this utility in Toad for downloading or uploading files, you can also include an FTP task in an Automation script.

To follow along with this tutorial, store a few Excel files in a folder on your remote FTP site. Include a text file with the following file name: Ready_to_Download.txt. In this tutorial, you will use this file as a signal that the Excel files are ready to download.

Configure FTP Connection

Before demonstrating this feature, you may need to configure an FTP connection.

1. Select View | FTP Connections.
2. In the FTP Connections pane, right-click the FTP Connections node and select Create.

3. In the Connections Properties dialog, enter a name for your connection, the server address, your username, and your password. Then, select the Save password option.

   Note: You must select the Save password option to use the connection in an Automation script.

Create Script

1. Select Tools | Automation to open a new Automation window.
2. Click the FTP activity in the Toolbox to add it to the Automation design window.
3. In the Activity Input tab, click in the Select an FTP connection field and select a connection.

4. Then click Define Operations. The FTP Options dialog displays. Use this dialog to define the options for your FTP task.

   Local folders and files are displayed in the left pane. Remote folders and files are displayed in the right pane. You can double-click a file to add it to the list of files to be downloaded (or uploaded) when the script executes.

5. In the Operation field, select Download to instruct the script to download files from the FTP site.
6. In the Remote directory, open the folder containing the Excel files that you want to download.

7. In this tutorial, you will use a filter to select files to download.

   1. Select the Use file mask option. The Files list is replaced with a pane containing File Mask options.

      Caution: Select Move files only if you want to move the selected files, not copy them.

   2. In the File mask field, enter the following: *.xlsx. This instructs the script to select all files with the extension .xlsx in that folder .

      You can also use a partial name and the asterisk wildcard character (*) to filter for similar file names, if necessary.

      

   3. You can further filter the list of files by specifying a date range, if necessary.
   4. Now select a folder in your Local directory into which you want to download the files.
   5. When you have finished specifying FTP options, click OK.
8. This step demonstrates the Transfer ONLY if option. You can use a signal file to instruct the script when to download. If the signal file (for example, Ready_to_Download.txt) is in the folder, the script can download the Excel files. (The signal file is only a technique used in the tutorial. It is not required for FTP downloading.)
   1. In the Activity Input tab, select the Transfer ONLY if option.

   2. In the drop-down list, select remote file exists. Then enter the file name of your signal file. In this case, enter Ready_to_Download.txt.

      

9. Click to save and run your script.

10. After the script executes, open the folder in your local directory to make sure that the files where downloaded.

Schedule A Script

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

Related Topics

Getting Started with Automation