Check for Source File Lock

[MSidat]

Hi All.

I am trying to implement some error checking in a series of TI's which are used in chores. In the whole pretty simple set of TI's to load GL data into a cube at set times during the day.

The source files gets FTP'd across from the GL to a shared folder which TM1 has access to and I need a way to check if the file has fully come across before the TI's start running.

I have done some simple tests with the FILEEXISTS function and that seems to return true even if the file is open by another user and I am not able to fully test but I am pretty sure it will also return true if the file is still in the process of coming across via FTP as the skeleton of the file will be created in an instance but the full transfer may take a few minutes.

To this end, just wondered if any of you guys and gals have a way of determining whether your source file is ready to be used?

Thanks in advance

[rmackenzie]

The source files gets FTP'd across from the GL to a shared folder which TM1 has access to and I need a way to check if the file has fully come across before the TI's start running.

I have done some simple tests with the FILEEXISTS function and that seems to return true even if the file is open by another user and I am not able to fully test but I am pretty sure it will also return true if the file is still in the process of coming across via FTP as the skeleton of the file will be created in an instance but the full transfer may take a few minutes.

To this end, just wondered if any of you guys and gals have a way of determining whether your source file is ready to be used?

It's a good question and there are several avenues open to you:

1. Here's a Microsoft technote on openfiles.exe which can be used to find out if a particular file is open on a server - but not a share - so it's usefulness to you may be limited. However, you could process the output from that command in a TI that would check for the presence of your source file and then exit the chore.

2. You can have a TI process for which the datasource can be set at runtime via a parameter (and then passed to DataSourceNameForServer) and does nothing in relation to that file. If the return code from this process is ProcessExitOnInit then, in your context, it is highly likely then the file is still being written to. Then, you can exit the chore without throwing any errors that would be cause by the downstream processes trying to read the file.

3. Look at job scheduling tools like Control M, ASG Zena etc as it is possible that your system is complex enough that your data load should happen via a push from an external system rather than a pull from within TM1. This way you totally avoid the kind of problem you are experiencing.

HTH

[MSidat]

Thanks for the reply Robin.

Decided to go with the second option you suggested alongside running a "Scheduler" Instance of TM1. Process would be for this instance to check for the relevant file and as soon as the file arrives and is valid, the TI can execute the relevant TI in the main Instance via TM1RunTi.exe.

[cdredmond]

Another possible optionif you wish to keep it simple:
Have the process which is sending the GL data file via FTP to the share also create a "flag file". This file would be a second and separate file name which is generated immediately after completing the FTP of the GL file. The Flag File could be empty or contain the current date time stamp or anything else you prefer as long as it is only a single, relatively short record that will write quickly.

Have the TI process search for the flag file instead of the main GL data file. Once it appears, you know you have a complete GL data file. Upon completion of the data import, delete the flag file for the next cycle.

Also check in the TI prolog if the process is currently running. This will require you to set a "Run Flag" in some cube or dimension somewhere when the TI begins processing data and turns off the run flag when it is complete. This will avoid duplicate processing of your GL data file.

I successfully implimented this approach years ago when the source system was ISAM files that could not be directly queried from TI and required a push approach.