SSIS For Loop and ForEach Loop to load multiple Flat Files data from different Folders to SQL Server

SSIS For and ForEach Loop Containers to Loop through multiple Text Files from different Folders and load data to SQL Server Destinations
Scenario :
Suppose we have two Folders having multiple input files(.txt type), one folder having the Product data and another Folder having the Service data as follows :

The different sets of input Files in each folder as follows :

From these input files we need to load only the files having naming like "Product-*.txt" and "Service_*.txt" which we need to Load to the respective SQL Server Destinations .
The Destination Tables :

--------------------------------------------------------------------------------------------------------------

SSIS Package Design :

Now we need to design the package as above based on above scenario as follows :

Phase-1 : Creation of the Variables Needed :

We need to create the below variables with direct value declaration as follows...

IncrementValue - This is a increment variable use in For Loop to store the count of Folders Looped. Initial value of this variable is "1".

TotalCount - This variable use in For Loop which represents the Total No.of Folders need to Loop. The value of this variable is "2" as we need to Loop through 2 Folders in our Scenario.

SrcFolderOne - The Path of the Folder which we Loop First.

SrcFileOne - The files having name like "Product_*.txt" which need to process by For Each Loop from ScrFolderOne.

SrcFolderTwo - The Path of the Folder which we Loop Second.

SrcFileTwo - The files having name like "Service_*.txt" which need to process by For Each Loop from ScrFolderTwo.

SrcFileName - This is a Run Time variable which we use in the For Each loop to store the selected input file name

Also need to Create two more Run Time Variables(Collection Variables) with expressions to capture the Folder and File Names selected by For Loop which passing in to For Each Loop  :

SelectFolderPath = @[User::IncrementValue]==1? @[User::SrcFolderOne]: @[User::SrcFolderTwo]

SelectFileName = @[User::IncrementValue]==1? @[User::SrcFileOne] : @[User::SrcFileTwo]

Phase-2 : Create the OLE DB Destination and Flat File Source Connections:

db_Tamatam - The connection to the Database.
Src_FlatFiles - The connection to the Flat file source .

As we dynamically need to loop through the source Flat files, we need to make the Connection String dynamic by using expression as follows :

ConnectionString= @[User::SelectFolderPath]+ @[User::SrcFileName]

here, The variable @[User::SelectFolderPath] is the selected folder path, whose value passed by For Loop.

The variable @[User::SrcFileName] is the selected file name, whose value passed by For Each Loop.

Phase-3 : Add the Execute SQL Task to Truncate your destination table before loading the data :

TRUNCATE Table [Tbl_SalesByProd]

TRUNCATE Table [Tbl_SalesBySvc]

Phase-4 : Add the For Loop Container to the Control Flow and setup Properties :

InitExpression : @IncrementValue=1

EvalExpression : @IncrementValue<=@TotalCount
AssignExpression : @IncrementValue=@IncrementValue+1

Phase-5 : Add the For Each Loop Container inside the For Loop Container in Control Flow and setup Properties :

In  "Collection" tab update the Collection Variables using the Expressions :
Directory : @[User::SelectFolderPath]

FileSpec : @[User::SelectFileName]
The values for these variables will be passed by For Loop, now the details will be reflect in For Each Loop as follows :

In "Variable Mappings" tab update variable name to store the File selected by the ForEach Loop;
Variable :: SrcFileName

Note:

The For Each Loop will take the @[User::SelectFileName] as "Product_*.txt from For Loop then it looks for a file like that in the @[User::SelectFolderPath] and selects the file.

Suppose if it first selects the File "Product_ABC123" from the source folder one, then that full file name will stored the variable SrcFileName.

Phase-6: Add the Data Flow task inside For Each Loop Container and define the flow :

Data Flow :

The Data Flow task will loads the Product and Service data from Source Flat files to SQL Server Destination tables based on the Conditional Split :

Conditional Split :
If @[User::IncrementValue] == 1 then loads the Data to Product table else loads the data to the default output which Service table.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------