How to read all folders and subfolders from Pentaho Kettle Get files with SFTP step

Question

The "Get files with SFTP" step is able to fetch all the files from the specified source path (over the FTP). But it is not able to read any of the folders exists at the source path. I tried with the Reg-Exp wild cards like .* or * or . etc, but no use.

In my use case, the source files always will come in one or multiple folders (like monthly transaction files in month specific folders, or year-wise and month-wise folders in multi-level folder hierarchy etc). If all these folders moved to my local machine, then I can use the "Get File Names" step to read the files from each folder and sub-folders.

Can anybody guide me how copy/move all the files and foldes from the source-path from this "Get files with SFTP" step?

Answer 1

Try VFS. I don't know for sure if it works but a little known amazing trick in PDI is that anywhere a file is referenced you can use a VFS URL and VFS Supports SFTP amongst many many other things.

http://infocenter.pentaho.com/help/index.jsp?topic=%2Fpdi_user_guide%2Fconcept_vfs_sftp_config.html

Answer 2

I believe you will need to iterate over the various sub-folders and keep iterating until you run out of folders. There's no "recursive" option for sftp, afaik.

Answer 3

I think you wont find a step for that but we can work around the issue. I'll sugest the same that we did on our jobs to handle a similar problem.

We used the Shell Step to run a shell script in linux that whould list a folder (with subfolders) and create a file with all the file paths that I want to read at a next step (one path/filename per line).

The next step of the job is a transformation that reads the file generated on that shell (we always generate in the same place) and after that transformation finish I have a delete file step that kills the file so we can generate another the next time.

Hope it helps. If you need more details, please let me know.