For Each File

For Each File is a great tool to use when you have a folder that is updated on a regular basis and the files within this folder need to be integrated into a database. This step will run an Ouvvi project for each file in a directory.

Configuration

Example

Below is an example of how the For Each file handler can be used and set up.

For the below example we are importing the files into a SQL table and then deleting the files from the directory once the import is complete. If the sync fails then the file is moved to a failures folder that can be processed separately.

Create User Setting

Ouvvi has the capability to define various user settings which can be called upon with a user defined name. For more information on user settings please visit the page here.

As we will be using a specific file location repeatedly we can define the path to the directory in the user settings.

This means we can use run time expansion e.g.{{FileLocation}} rather than typing the path each time it is needed.

Create the Processing Project

The For Each File handler will feed into a defined project. To create this processing project please follow the steps below:

1. Log Message

Using a Log Entry step we can add a message to the log to show the file path of the file to be precessed.

2. Data Sync Project

To synchronise the file to our chosen source we will need to add a Data Sync project step and configure the project.

For this example we are using CSV files that will be synced to a SQL Table.

For this to work with the For Each file handler, Ouvvi will update the project at runtime with the value of {{CONTEXT}} from the project properties.

At runtime Ouvvi will inject this {{CONTEXT}} value into the Data Sync Project Properties property named Auto_Context. We will need to use Project Automation to get this value and update the source CSV Connection Filename so that it will process the correct file.

During Design Time of the project, the value of Auto_Context is empty and this will break our project. Therefore we only need to set the FileName property when we are running under Ouvvi. This can be achieved using the IsInteractive project property, that tells us if we are running in design mode or not.

In Project Automation set the FileName property of the source connection to the Path that is supplied by the Auto_Context project property value.

public override void Start()
{
    DataSourceA.FileName = IsInteractive ? DataSourceA.FileName : Properties["Auto_Context"];
}

3. Delete the File

To delete the file after processing we will add a File Delete Step that calls {{CONTEXT}}.

4. Failure Folder

To move the failures to the failure folder we can use the File Copy Step.

5. Test

Test that everything has been configured correctly. The log should show the log step writing the file path to the log, the data sync step processing the file additions, the delete step deleting the file/s from the directory and the failures step being skipped if project did not fail.

Configure the For Each Project

For Ouvvi to gather the context value of each file in the directory we need to configure the For Each Project. This project will look into the file directory, defined by the user, and pass the value of context (Full Path, File Name or Relative Path) onto the processing project we configured earlier.

The project should look something like this:

Add a Trigger

To finish we can add a Folder trigger to the For Each project, to have the project run every time a new file is added to the directory. The Folder trigger detects changes in the modified date of the files in the directory folder.

Please note that the modified time of the files in the directory needs to be greater than the last time the trigger fired. Copying files into the directory may not modify the modified date/time and therefore may not fire the trigger.

The Time Between Trigger Capture is to prevent the trigger from firing multiple times if a large file is being loaded into the file directory. You can set this to be any length of time you prefer, in the screen capture below we have gone for 5 seconds.