Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

Part Number B31278-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

19 Transferring Remote Files


Developers at your company designed mappings that extract, transform, and load data. The source data for the mapping resides on a server separate from the server that performs the ETL processing. You would like to create logic that transfers the files from the remote computer and triggers the dependent mappings.


In Warehouse Builder, you can design a process flow that executes file transfer protocol (FTP) commands and then starts a mapping. For the process flow to be valid, the FTP commands must involve transferring data either from or to the server with the Runtime Service installed. To move data between two computers, neither of which host the Runtime Service, first transfer the data to the Runtime Service host computer and then transfer the data to the second computer.

You can design the process flow to start different activities depending upon the success or failure of the FTP commands.

Case Study

This case study describes how to transfer files from one computer to another and start a dependent mapping. The case study provides examples of all the necessary servers, files, and user accounts.

Your objective is to create logic that ensures the flat file on salessrv1 is copied to the local computer and then trigger the execution of the salesresults mapping.

To transfer files and start a dependent mapping, refer to the following sections:

  1. "Defining Locations".

  2. "Creating the Process Flow"

  3. "Setting Parameters for the FTP Activity"

  4. "Configuring the FTP Activity"

  5. "Registering the Process Flow for Deployment"

After you complete the instructions in the above sections, you can run the process flow.

Creating the Process Flow

Use the Process Flow Editor to create a process flow with an FTP activity that transitions to the salesresults mapping on the condition of success. Your process flow should appear similar to Figure 19-1.

Figure 19-1 Process Flow with FTP Transitioning to a Mapping

Description of Figure 19-1 follows
Description of "Figure 19-1 Process Flow with FTP Transitioning to a Mapping"

Setting Parameters for the FTP Activity

This section describes how to specify the commands for transferring data from the remote server salessrv1, to the local computer. You specify the FTP parameters by typing values for the FTP activity parameters on the Activity View as displayed in Figure 19-2.

Warehouse Builder offers you flexibility on how you specify the FTP commands. Choose one of the following methods:

Example: Writing a Script in Warehouse Builder for the FTP Activity

The following example illustrates Method 1 described above. It relies on a script and the use of substitution variables. The script navigates to the correct directory on salessrv1 and the substitution variables are used for security and convenience.

This example assumes a Windows operating system. For other operating systems, issue the appropriate equivalent commands.

To define a script within the FTP activity:

  1. Select the FTP activity on the canvas to view and edit activity parameters in the Available Objects tab of the Explorer panel in the Process Flow Editor.

  2. For the COMMAND parameter, type the path to the FTP executable in the column labeled Value. If necessary, use the scroll bar to scroll to the right and reveal the column labeled Value.

    For windows operating systems, the FTP executable is often stored at c:\winnt\system32\ftp.exe.

  3. For the PARAMETER_LIST parameter, type the Task.Input variable.

    When defining a script in Warehouse Builder and using Windows FTP, you must type ?"-s:${Task.Input}"? into PARAMETER_LIST.

    For UNIX, type ?"${Task.Input}"?.

  4. Navigate and highlight the SCRIPT parameter. Your Available Objects tab should display similar to Figure 19-2.

    Figure 19-2 Activity View for FTP Activity Using a Script

    Description of Figure 19-2 follows
    Description of "Figure 19-2 Activity View for FTP Activity Using a Script"

  5. Click the Ellipses displayed to the right of the Value field displayed in the Object Details panel.

    Warehouse Builder displays the SCRIPT Value editor. Write or copy and paste FTP commands into the editor.

    Figure 19-2 shows a script that opens a connection to the remote host, changes the directory to the local computer, changes the directory to the remote host, transfers the file, and closes the connection.

    Notice that the script in Figure 19-3 includes ${Remote.User} and ${Remote.Password}. These are substitution variables. Refer to "Using Substitution Variables" for more details.

    Figure 19-3 SCRIPT Value Editor Using Substitution Variables

    Description of Figure 19-3 follows
    Description of "Figure 19-3 SCRIPT Value Editor Using Substitution Variables"

Using Substitution Variables

Substitution variables are available only when you choose to write and store the FTP script in Warehouse Builder.

Use substitution variables to prevent having to update FTP activities when server files, accounts, and passwords change. For example, consider that you create 10 process flows that utilize FTP activities to access a file on salessrv1 under a specific directory. If the file is moved, without the use of substitution variables, you must update each FTP activity individually. With the use of substitution variables, you need only update the location information as described in "Defining Locations".

Substitution variables are also important for maintaining password security. When Warehouse Builder executes an FTP activity with substitution variables for the server passwords, it resolves the variable to the secure password you provided for the associated location.

Table 19-1 lists the substitute variables you can provide for the FTP activity. Working refers to the computer hosting the Runtime Service, the local computer in this case study. Remote refers to the other server involved in the data transfer. You designate which server is remote and local when you configure the FTP activity. For more information, see "Configuring the FTP Activity".

Table 19-1 Substitute Variables for the FTP Activity

Variable Value


The root path value for the location of the Runtime Service host.


The host value for the location involved in transferring data to or from the Runtime Service host.


The user value for the location involved in transferring data to or from the Runtime Service host.


The password value for the location involved in transferring data to or from the Runtime Service host.


The root path value for the location involved in transferring data to or from the Runtime Service host.

Configuring the FTP Activity

As part of configuring the complete process flow, configure the FTP activity.

To configure the FTP Activity:

  1. Right-click the process flow on the navigation tree and select Configure.

  2. Expand the FTP activity and the Path Settings. Warehouse Builder displays the configuration settings.

  3. Set Remote Location to REMOTE_LOCATION and Working Location to LOCAL_LOCATION.

  4. Click to select the Use Return as Status. This ensures that the process flow uses the FTP return codes for determining which outgoing transition to activate. For the process flow in this case study, shown in Figure 19-1, if FTP returns a success value of 1, the process flow continues down the success transition and executes the salesresults mapping.

Registering the Process Flow for Deployment

After you complete these instructions, you can deploy and run the process flow. To deploy the process flow, start the Deployment Manager by right-clicking and selecting Deploy from either the process flow module or package on the navigation tree. The Deployment Manager prompts you to register the REMOTE_LOCATION and the LOCAL_LOCATION.

Figure 19-4 shows the registration information for the REMOTE_LOCATION. For the LOCAL_FILES, only the root path is required.

Figure 19-4 Example Location Registration Information

Description of Figure 19-4 follows
Description of "Figure 19-4 Example Location Registration Information"

Now you can run the process flow.

Defining Locations

Locations are logical representations of the various data sources and destinations in the warehouse environment. In this scenario, the locations are the logical representations of the host and path name information required to access a flat file. Warehouse Builder requires these definitions for deploying and running the process flow. When you deploy the process flow, Warehouse Builder prompts you to type the host and path name information associated with each location. You must define locations for each computer involved in the data transfer.

To define locations, right-click the appropriate Locations node in the Connection Explorer and select New. For salessrv1, right-click Files under the Locations node and create a location named REMOTE_FILES. Repeat the step for local and create the location LOCAL_FILES.