Dynamically pass filepath in Data Source using a system property or script include possible ?

[Nikhil Raj]

Hello,

I have a SFTP folder created where within that folder there are multiple subfolders which will contain same set of CSV files. Below is the example structure of SFTP folder considering ABC as the main SFTP folder

 ABC --> ABC-1

                           (files within this folder - XYZ.csv, VPC.csv, Network.csv)

                ABC-2

                           (files within this folder - XYZ.csv, VPC.csv, Network.csv)

                ABC-3

                           (files within this folder - XYZ.csv, VPC.csv, Network.csv)

Now files within these folders will remain same format and the subfolders name might add up in the future but will be fixed without any changes. Now in the data source I have used File Retrieval method as SFTP, and I give Server, port, Username and Password to connect to this folder and when I give exact file path it works and loads data through import set and via transform map.

Issue 1:

I would have to create multiple data sources if there will new Subfolders like ABC-4, ABC-5 etc., and goes on. I want to avoid that, since the files(.CSV) and file names remain same. Using the same data sources is it possible to run them  somehow by passing the file path dynamically ?

Issue 2:

Even if I consider creating each data source for all the files in all different folders the problem is that some folder names are very lengthy along with file name which exceeds the max length limit. With the data sources' file path field length which is limited to 100 and I am not able to enter the full path. Also I am not allowed to increase its max length, but somehow will have to use a variable which gets the values dynamically sets the path in 'filepath" field so that it gets the file name also with the correct path. 

Could you please help me with the solution for these problems ? Either one of the solutions for above mentioned problem would help me solve the entire issue.

[Ratnakar7]

HI @Nikhil Raj ,

Yes, it is possible to pass the file path dynamically using a system property or script include.

For Issue 1, you can create a script include that returns the file path based on the subfolder name. For example, if you have a system property that stores the current subfolder name, you can use a script include to dynamically generate the file path based on the current subfolder name. Then, in the data source configuration, you can use a dynamic parameter to set the file path. This way, you can use the same data source for all subfolders.

Here is an example script include that generates the file path:

var SftpDataSourceUtil = Class.create();

SftpDataSourceUtil.prototype = {

  initialize: function() {
  },

  getFilePath: function(subfolderName) {
    var server = 'sftp-server';
    var port = 22;
    var username = 'user';
    var password = 'password';
    var path = '/ABC/' + subfolderName + '/';
    var filename = 'filename.csv';
    return 'sftp://' + server + ':' + port + path + filename + '?username=' + username + '&password=' + password;
  }

};

In the data source configuration, you can set the file path as a dynamic parameter using the following syntax:

${JS: new SftpDataSourceUtil().getFilePath(gs.getProperty('current.subfolder'))}

For Issue 2, you can use a similar approach to generate the file path dynamically based on a system property that stores the full path. You can use a script include to read the system property and return the file path. Then, in the data source configuration, you can use a dynamic parameter to set the file path.

Here is an example script include that generates the file path based on a system property:

var SftpDataSourceUtil = Class.create();

SftpDataSourceUtil.prototype = {

  initialize: function() {
  },

  getFilePath: function() {
    var server = 'sftp-server';
    var port = 22;
    var username = 'user';
    var password = 'password';
    var path = gs.getProperty('file.path');
    var filename = 'filename.csv';
    return 'sftp://' + server + ':' + port + path + '/' + filename + '?username=' + username + '&password=' + password;
  }

};

In the data source configuration, you can set the file path as a dynamic parameter using the following syntax:

${JS: new SftpDataSourceUtil().getFilePath()}

Make sure to set the 'file.path' system property to the correct path before running the data source.

If my response was helpful in resolving the issue, please consider accepting it as a solution by clicking on the ✅Accept solution button and giving it a thumbs up 👍. This will benefit others who may have a similar question in the future.

Thank you!

Ratnakar

[Community Alums]

Hi @Ratnakar7 ,

I tried your solution. But I am not seeing the below line working when having this in the file path field on the data source.

${JS: new SftpDataSourceUtil().getFilePath()}

It seems when the import job runs to use the data source, it is returning the script string as file path instead of dynamic file name. I got below error: -

com.glide.db.impex.datasource.DataSourceException: java.io.IOException: FtpWorkflowException --> Return Value: 550 Description: D:\UserData\Retention\7\ajeetchaubey\${JS: new SftpDataSourceUtil().getFilePath()} (The filename, directory name, or volume label syntax is incorrect) ("/ajeetchaubey/${JS: new SftpDataSourceUtil().getFilePath()}")

Just curious to know how you got this working for you.

Regards,

Ajeet