How to read dynamic file naming for csv file in ssis?

Steps for dynamically load the flat file into the destination.

• Use the Foreach loop container
• In Collection tab, Select Enumerator as Foreach File Enumerator and Configure as follows.

Folder: Location of your source files

Files : File-*.txt [Since your file naming is something like File-ddmmyyyyhhmmss.txt]

Retrive File Name : Select Fully qualified Name [which brings entire location of your file]

• In Variable Mapping, Create a variable to store the Full file path and configure as shown in the screenshot.

• Create a Flat file connection using sample file.
• Select a Flat file and go to properties. Find the expression property and map the variable to the connection string as shown in the following screenshot.

Important Note:

If you want to validate the file name and existence of the file name before loading into the destination table. Use the script component inside the For each loop container and validate.

Hope this helps.

Here's a step by step guide

1. Use the Foreach Loop Container (File Enumerator type) , set your Files mask as File-??????????????.txt
2. Map a string variable to capture the match files.
3. Add a Script Task inside this container to parse the string variable to match the actual file naming convention (hint: use regex). Any successful matches will act like a filter larger list of files found by the Foreach Loop Container.

Refer this learning series from Microsoft:

https://learn.microsoft.com/en-us/sql/integration-services/lesson-2-adding-looping-with-ssis?view=sql-server-2017