Loading a Target Table Using the File Listener

You can use DataMigrator to build a flow that automatically processes source files as they appear in a designated directory. This can be useful if, for example, source files are transferred by FTP from various locations throughout the day and need to be processed immediately.

Before you create and run the data flows discussed in this section, you must create the sample data and synonyms. For more information, see How to Create Sample Procedures and Data for Iteration. This will create source and target synonyms and four source data files used in the example.

To implement and run a real time flow:

  1. Create an application directory to receive files.
  2. Create a synonym with File Collection Parameters.
  3. Create a Data Flow that uses the File Listener.
  4. Run the flow and drop the files.

This flat file synonym corresponds to the layout of the source files that will appear in the designated directory. The flat file source is loaded to the target of your choice in real time.

Refer to the sample flow DMFLORD in the IBISAMP directory for the completed example.


Top of page

Example: Create Listen and Archive Directories

For DataMigrator to automatically process files when they arrive in a directory, you need to designate a directory for that purpose. It could be any directory that your server can access. For this example, we will create an application directory called listen.

In addition, if you want to keep a copy of the files after they are processed you need to designate a directory for that purpose too. For this example, we will create an application directory called archive.

  1. Right-click Application Directories, select New, and then click Application Directory.

    The Create New Application dialog box opens.

  2. For Application name, type listen.

    De-select the Add directory to APPPATH check box.

    Click OK.

  3. Repeat steps 1 and 2 to create a directory called archive.

Top of page

Example: Create a Synonym With File Collection Parameters

Next you need a synonym with File Collection Parameters to control how the file listener process files. We will start with a synonym that describes just the record layout of the source files and add the parameters.

  1. On the Home tab, in the Filter group, click Synonyms to ensure that synonyms are displayed. Then, in the navigation pane, expand the Application Directories folder and the ibisamp folder.
  2. In the ibisamp folder, double-click dmordflx to open the synonym. Note that the column named SOURCEFILE has a special ALIAS of INSTANCE. This field does not exist in the source file itself, but it will contain the name of the source file when it is read.
  3. Expand the File Collection Parameters group.
  4. Fill in the synonym parameters, as shown in the following image.

    Properties Pane

    This creates a synonym that listens on the listen directory for files with names that start with dmord and an extension of data polling every 5 seconds until 60 seconds have passed without a new file. As files are processed they are archived to a directory archive. Files are processed as they arrive until 5 files or 50 records are processed.

  5. Click Save As in the Quick Access Toolbar to save the synonym in the application directory of your choice.

Top of page

Example: Create a Flow with a File Listener Source

A flow with a File Listener source can be either a Data Flow or a Direct Load Flow. For this example, use a Direct Load Flow.

In the DMC:

  1. Right-click an application directory, select New, and then click Direct Load Flow. The Direct Load Flow opens.
  2. From the browser window, drag the source object DMORDFL that you created from its application directory to the left of the center line in the workspace.
  3. Drag the target object DMORDF from the ibisamp application directory to the right of the target line in the workspace.
  4. Right-click the target object DMORDF and click Properties.

    The Properties dialog box opens.

  5. For Prior to Load Option, select Truncate table, if available. If not, select Delete all rows from table or Drop table. Click X to close the Properties page.
  6. Right-click the target object and click Target Transformations.

    The Transformations window opens.

    Click the Automap Automap Button button and click OK.

  7. Save the flow as DMXFLORD.

Top of page

Example: Create a Synonym for a File Listener with a Trigger

You can use DataMigrator to build a flow that automatically processes source files as soon as a specific Trigger file is detected in an application directory. A trigger is typically used when processing needs to be delayed. For example, when using FTP to copy files, if you wait until this secondary file appears while a large file is being copied into a directory, the trigger file indicates when processing can begin.

The following example shows how to create such synonym in a baseapp application using an Adapter for Delimited Flat File. You can also create such synonym using an Adapter for a Flat File or in any other application directory.

  1. Follow the steps for creating listen and archive directories as described in the Create Listen and Archive Directories example.
  2. In the DMC, right-click the baseapp application directory, select New and then click Synonym (Create or Update).

    The Select adapter to configure or Select connection to create synonym dialog box opens.

  3. Select the connection from the Delimited Flat File node and click OK.

    The Select Data File dialog box opens,

  4. Disable the Create Multiple Synonyms checkbox.
  5. Click the ellipses in the Data File field and select the data file from the Listen directory, as shown in the following image.

    DataMigrator creates the Master File for the File Listener synonym for the synonym later, based on this file.

  6. The Select Processing Options dialog box opens, as shown in the following image.

    Enter the following values:

    1. Select Collection of files from the drop-down menu on the Sleect Processing Options screen.
    2. Select Trigger from the Data File Pickup Strategy drop-down menu.
    3. Enter the extension in the Trigger File Extension field. For example, if the file name that you listen to is myfile.csv, the trigger file name is myfile.csv.trg. The value for the Trigger File Extension will be trg.
    4. Select Archive from the Processed Data Files Discard Strategy drop-down menu.
    5. Enter the name of your Archive directory in the File Listener Archiving Directory field.

    The options should appear as shown in the following image.

  7. Click Next.

    The Define Collection dialog box opens.

  8. Enter the following values:
    1. In the Directory or Application field, enter the name of your Listen directory.
    2. Enter a name or mask in the Name field.
    3. Enter the extension of a file, or a mask, in the Extension field.

    The options should appear as shown in the following image.

  9. Click Next.

    The Select Synonym Candidates dialog box opens.

  10. Enter all attribute values that are needed for the Delimited Flat File.
  11. Click Next.
  12. Click Create Synonym.

Top of page

Example: Running a Flow With a File Listener Source

In this example, we will start the flow and then copy source files to the listen directory.

In the DMC:

  1. Right-click DMXFLORD and click Submit.
  2. Copy the following files, one at a time, from the ibisamp application directory to the listen application directory:

    dmordbos.dat, dmorddal.dat, dmordorl.dat, dmorstl.da

    Tip: For a Windows based server, you can use the Edit and Copy/Paste functions.

    Note: As the files are processed, you should see them disappear from the listen directory. This could take up to the timeout interval specified in the synonym.

  3. Return to the DMC. The source synonym has a timeout of 60 seconds, so you will see the Processing dialogue until the time has elapsed.
  4. When the flow completes, on the Flow tab, in the Reports group, click View Last Log. You should see a report, like the following image, that shows all total number of rows processed.

    View Log Report

    With the Discard option set to Archive, after each file is processed, it is copied to the archive directory, as shown in the following image.

    archive

    These files can be deleted, as they are no longer needed.


iWay Software