Bring Foreach Loop Container to Control Flow Pane and Set Directory=VarSourceFolder as shown below. As we are going to read only .txt files. Provide *.txt under Files text box.
Go to Variable Mapping and create a new variable VarFilePath that will hold fully qualified path on each iteration.
Bring Data Flow Task inside Foreach Loop Container and open it by double clicking. Inside Data Flow Task,Drag Flat File Source and create connection string by using any file from Source Folder.
Click on Columns to make sure everything working as expected. You can preview some data by clicking on Preview.
Drag Derived column Transformation and connect Flat File Source to it. Generate a new column by using the VarFilePath as shown below
I have converted the value of variable to Varchar(100) by using Cast(DT_STR,Length,Code)
Drag OLE DB Destination and connect Derived Column Transformation to it and choose dbo.SaleRegion as our destination table and map the input column to destination columns.
As we have created Flat File Connection to Asia file but the file name will be changing with each iteration so we have to go to Flat File Connection and write expression for connection string to use the VarFileName variable.
Set DelayValidation to True.
Run the SSIS Package and check if the data from all files are loaded to table.