After upgrading Matillion from version 1.68.21 to 1.75.0, we're experiencing an issue with the "File Iterator" component when selecting "SFTP" as the Input Data Type. The component isn't iterating through any files from SFTP. However, when we use the Data Transfer component with the same SFTP connection, it works fine, and we can successfully retrieve files from the SFTP server. What will be the solution for this issue?
I am also facing the same issue. The file iterator is not iterating and is always generating 0 iterations.
How can the data transfer component be used to transfer multiple files to S3 from SFTP ?
On further verification, it is found that the value from the variable is not considered in the iterator for the Filter Regex property.
In the Filter Regex property, I used LEDGER_TB_${vTBdate}.* whereas vTBdate will have current date and it stopped working after the upgrade. I changed the value to .* and iterator works and picks up all the files in the specified folder.
But I need to use it with the variable only.
To overcome this issue we are currently using an alternative method. I hope it might help you.
We have a specific filename pattern like 'File_name_yyyymmdd.csv' where the filename remains constant, and only the file date changes daily. To manage this, we maintain a file monitor table that stores details of the most recently loaded file, including the file date.
Here’s how we handle the process:
We created a view based on the file monitor table. This view retrieves file dates that are greater than the last loaded file date and fetches files within a 5-day range.
Instead of using the File Iterator, we use the Table Iterator component to pass these dates (retrieved from the view) into the Data Transfer component.
The Data Transfer component then picks up the corresponding files (with dates from the Table Iterator) and loads multiple files to S3 accordingly.
This method allows us to effectively manage and load files with dynamic filenames based on date ranges, even without using the File Iterator.
Thanks for the reply and the detailed explanation. This is an amazing workaround you found out.
To summarize, the table iterator works with SFTP whereas the file iterator fails. Strange behaviour post-upgrade.