Directory Monitor

  • Advanced ETL Processor - is ready for the users.

    The most important change that 64-bit version can now execute both 32bt and 64bit scheduled actions, here a screenshot of the execution log:


    Other changes are:

    • Improved: Directory Monitor
    • Added: New events to watch to Directory Monitor: add, delete, modify and rename files
    • Added: Wait and Timeout option to Directory Monitor
    • Improved: UnInstall process
    • Fixed: Salesforce Bulk API load issues
    Download Free Trial Learn More About Our ETL Software
  • Background:

    This article was created as an answer to one of our ETL forum posts, we hope it will help a lot of users in a similar situation.

    "The scenario is based on files landing in a monitored directory. At this stage, I am not worried about volume or frequency. But will be handling each file individually for internal reasons. Each file has a unique name but a common format of xxx-yyyymmddhhmmssnnnn.ext

    "xxx" represents a client prefix (eg. abc, bcd, cde). The format of each file for the same client will be the same. But the format is different for each client. The file extension '.ext' will generally be .csv, .xml, or even .xls.

    The file transformations between clients will be different. However, the output is always the same format and the target is a SQL Server database table.

    I am developing each of the transformations in isolation, but with the same basic structure.

    My idea is to have a single monitor looking at a single folder for *.* That then executes a single Package based on the creation of a file. The Monitor passes the file to the Package that then does some environment management, put the source file in a staging location, then based on the file prefix conditionally (Probably through a CASE action) call the associated transformation and load to the SQL server and then clean up."


    The monitor (Directory monitor) is a windows service which is constantly monitoring a directory for changes. When it happens it adds predefined action to the execution queue. Monitor passes the file name as a variable into the action, this variable name is <Directory Monitor File Name>.


    Example of data warehouse workflow

    "Get ClientID" script: extracts first three characters of the file name. The Result is stored in '<ClientID>' variable.

    FileName: String;
    ClientID: String;
    FileName := GetVariable('<Directory Monitor File Name>');


    Check Client directory: checks if client directory exist and if it does not it creates it together with subdirectories.

     Every transformation has an ID. We use this ID to execute specific transformation for a specific customer.

    Table Transformations holds mapping between ClientID and TransformationID:

    CREATE TABLE [dbo].[Transformations]
    ([ClientID] [char](3) NOT NULL,[TransformationID] [int] NULL, 
    CONSTRAINT [PK_Transformations]


    "Insert Into Transformations" sql script inserts a record for missing clients:

    insert into Transformations(
    values ('<ClientID>',0)


    Once file is ready for ready processing it is moved into Staging Area.

    Get transformation ID: executes the following SQL:

     Select TransformationID
    from Transformations
    where ClientID='<ClientID>'

    The result is saved into <TransformationID> variable.

    Execute Transformation script:


    Upon completion the file is moved into Processed Area.

    Directory Structure:


    Variable as Mask:


    Download Free Trial Learn More About Our ETL Software
  • What Directory Monitor For

    Directory Monitor is a windows service which tracks folder changes, such as; file creation, removal, modification or rename. It is able to handle multiple locations at once. There is the ability to watch network shares in addition to local folders. Directory monitor does not execute anything, we have a separate windows service for it, called execution agent. When change happens, directory monitor inserts a record into queue table. There is no need to restart Directory monitor service when user amends the settings.

    How Directory Monitor Works


    Holds the overall status of directory monitor: is it enabled or not and settings for individual threads events to monitor for: filter, waits


    Create, amend and delete monitors settings.

    Management thread

    Responsible for creating, updating and destroying monitor threads.

    Monitor thread

    Monitors single directory for changes.
    When an event happens, it inserts a record into queue table.

    File usage thread

    Creates and runs file usage tasks
    When create event happen it might take some time to release the file.
    (It might take 5 minutes to copy a file)

    File usage task

    The task is just a procedure which checks if the file is open. For example, one task checks filea.txt, another check filleb.txt and so on. Threads are very heavy and there is OS limitation how many threads we can create. Creating thousands of threads is definitely a stupid idea. The task is very light and while one task is waiting for x milliseconds another one is being executed. The way it works, it checks if the file is open if it is, it sleeps for some time and checks it again. Once the file is closed or a timeout happens it inserts a record into queue table. Checking is done by opening a file for writing.

    Download Free Trial Learn More About Our ETL Software
  • Monitoring Directories for New files

    Directory Monitor is windows service which executes predefined action when new file is added to the directory. Both local directories and network shares (using UNC path) can be monitored.

    All our ETL tools have no limitations, that means you can load your data today and solve all the data quality problems right now.

    Download Free Trial Learn More About Our ETL Software

We have 158 guests and one member online