How to use a Dynamic Path in a TI for a Data Source

Ever wondered how you can vary the data source for a Turbo Integrator process inside the actual process so it becomes a dynamic path? Well here is a method.

Firstly, define a bunch of variables:

sCurrentMonth = CellGetS ( 'System Assumptions', 'No Year', 'No Month', 'Current Month', 'String' );
sFileDir = '\\servername\folder\';

sFileName = sCurrentMonth | ' ' | SUBST(sCurrentYear,3,2) | ' Employees.csv';
sFilePath = sFileDir | sFileName;

DatasourceNameForServer and DatasourceNameForClient

Then use DatasourceNameForServer and DatasourceNameForClient to force the TI to use specific files for both the Datasource Name for the server and for the client.

DatasourceNameForServer = sFilePath;
DatasourceNameForClient = sFilePath;

Make the Path Dynamic to Load Data

Why is this useful?  Well there are a couple of reasons:

  1. Where you have a file that is being automatically created and includes (in our case) the month and year in the filename, this is very useful.  It then allows us to keep historic versions of the source files in case we need to reload them.
  2. If you have a Dev and Prod environment, for example. You could have the server name as an entry in a System Control cube. Then retrieve that path from System Control and use it dynamically in a TI. The code for this is outrageously simple:
sOutputDir = CellGetS('System Control', 'Output Dir', 'String');
sOutputFileName = 'CubeLoad - GL - From Op PlanOperating Plan.txt';
sOutputFilePath = sOutputDir | '\' | sOutputFileName;
DatasourceNameForServer = sOutputFilePath;

You might also like