SSIS Foreach Loop Container: How to loop through files, load one by one, and archive folder using SSIS

In a previous post, I answered a question regarding SSIS packages using 32-bit drivers and 64-bit machines. In this post, I’ll address another common question related to using SSIS for looping through and loading files to move them to an archive folder by configuring a Foreach Loop container.

What Is Foreach Loop in SSIS?

Processing files in a specific folder in DTS takes a good amount of work. This same process in SSIS is much easier. You can use an SSIS Foreach Loop container to define a control flow task to loop through different types of enumerators, such as files, in a specified folder. 

A Foreach Loop container is like a For Loop container but differs when it comes to the number of times the loop executes. A For Loop container uses expressions to define a fixed number of times to execute. A Foreach Loop container executes as many times as needed over a specified enumerator type. Common types of Foreach Loop container enumerators include:

• Foreach File enumerator
• Foreach ADO enumerator
• Foreach Item enumerator

Install the Sample Package

The following sample SSIS Package shows you how to process each file (Nightly_*.txt) in C:\SSIS\NightlyData. After each file is processed, it's moved to the Archive folder.

Complete the following six steps to set up the test Sample SSIS Package:

1. Download and Extract the For_Each_Loop_File_Test.zip file.
2. Create a folder C:\SSIS.
3. Create a Folder C:\SSIS\NightlyData.
4. Create a Folder C:\SSIS\NightlyData\Archived.
5. Copy the provided sample files to C:\SSIS\NightlyData.
6. Open FAQTest.sln and analyze or run the Sample Package to test it.

SSIS Foreach Loop Container Configuration Instructions

Complete the following step-by-step instructions for this sample to configure the SSIS Foreach Loop container:

Define Variable Mappings

The sample package uses five variables with the following settings: 

Note: The “Is Expression” column refers to the EvaluateAsExpression property of the SSIS variable.

Define Connections

The sample package requires two connections: OLE DB and flat file. To define these connections, you must:

1. Create an oledb connection ((local).tempdb) to use tempdb database.
2. Create a flat file connection (NightlyDataFile) for any available file in the SourceFolder (in this example, use the file name C:\SSIS\NightlyData\Nightly_01.txt).
3. Configure the flat file ConnectionString Property as an expression to make the flat file connection dynamic.

Note: The flat file connection needs to be dynamic, so one connection can load several files from the source folder. The varFilePath variable will be updated on each iteration of the file in the Foreach Loop container.

Execute SQL Task (Create or Truncate Table)

Create an Execute SQL task to execute the following SQL Statement:

if object_id('tempdb.dbo.Staging_Customers') is null
begin
create table tempdb.dbo.Staging_Customers
(
 FilePath varchar(255)
,CustName varchar(255)
,CustPhone varchar(255)
,CustEmail varchar(255)
)
endtruncate table tempdb.dbo.Staging_Customers

Note: Make sure you select the tempdb connection for this task.

Foreach Loop Container (Load Nightly Data Files)

Add the Foreach Loop Container and double-click it to open the properties dialog box. Apply the following settings:

Task Factory

Dozens of high-performing SSIS components that help you save time managing ETL tasks.

Data Flow Task (Load Data File)

Add the Data Flow task inside the Foreach Loop container by completing the following steps:

1. Double click the dataflow and add the flat file source.

2. Add the OLE DB Destination. Connect the flat file source and the OLE DB Destination.

3. Double click on the OLE DB Destination to select tempdb connection, then select mapping and configuration.

File System Task (Move File to Archive)

Place the File System Task to move the processed file to the archive folder. Specify the properties as displayed below:

Connect All Task and Testing

Connect all tasks and run the package. If everything is configured correctly, the files should load into the staging table and then move to the Archived folder.

Your task is complete.

About Task Factory

Task Factory offers dozens of high-performance SSIS components built to accelerate ETL processes. With Task Factory, you can connect to nearly any data source with ease.

To learn more about how Task Factory can help you, see the Task Factory product page.