Sort Filename and Load Earlist based on the filename

Question

Hi All,

Lets say I have SSIS package as below. I'm using foreach loop container to load the TXT file that contain the first name RL_TB_ISM_VIXTRANS_ using the script task. So lets say I have 3 file contain the firstname regardless the date it will be loaded. But how can I ensure load the earliest file first followed by others in ascending order for example below the earliest file is RL_TB_ISM_VIXTRANS_20220824 then RL_TB_ISM_VIXTRANS_20220825 then followed by RL_TB_ISM_VIXTRANS_20220826. Appreciate your response. Thanks and Regards.

Answer 1

Hi @jn93 ,

Based on my test, the files are always processed alphabetically on filename in ascending order in Foreach Loop Container. But there is no article to proof the task return them that way. The following similar two threads are for your references:

https://www.sqlshack.com/using-ssis-foreach-loop-containers-process-files-date-order/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8d0c98e6-2acc-494c-b03c-8351c608074f/for-each-file-ordering?forum=sqlintegrationservices

Someone said that you could first use your foreach loop container to get the list of files, and then populate a database table. Then, outside of the foreach loop, use an execute SQL task to select from that table using an ORDER BY. Load an object variable with that result set. Then use a second foreach loop to spin through that object variable (ADO.net recordset). From here you can perform the work you desire.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.