Foreach Loop in SSIS

Foreach Loop

• For loop container loops till a fixed count.
• For-each loop containers load and traverse multiple files, folders, and records.
• In this article using the ForEach loop container, we load multiple files using SSIS.
• The below screenshot shows the vendor and product text file. These files add to SQL Server.

text file

• In the SSIS firstly we drag and drop the data flow task.

DATA FLOW TASK

• After that, we drag and drop the flat file source in the SSIS toolbox and configure the file.

FLAT FILE SOURCE

• Right-click on the Flat File Source component → Goto Edit option → And in the Connection Manager we connect to a text file.

CONNECTION MANAGER

• For connecting text file click on the New and browse the text file.

FLAT FILE CONNECTION MANAGER EDITOR

• After that check the column in the Column option. Then click on the OK button.

FLAT FILE CONNECTION MANAGER EDITOR

• In the SSIS toolbox, we drag and drop ADO NET Destination for text file data store in the SQL Server.

ADO NET DESTINATION

• By configuring we right-click on ADO NET Destination → Goto Connection Manager →New

ADO NET DESTINATION EDITOR

• Here enter the Server name and Database name and click on OK.

CONNECTION MANAGER

• Here configure the properties used to insert data into a destination using ADO.NET provider.

ADO NET DESTINATION EDITOR

CREATE TABLE QUERY

• After configuring the connection manager we check the Mappings. And click on OK.

MAPPING

• Select ForEach loop container from SSIS ToolBox.

FOR EACH LOOP CONTAINER

• We Create a variable by right click on the white space area.

CREATE VARIABLE

• After that, we configure the For each loop. Goto Collection and the Enumerator select the Foreach File Enumerator also we browse the folder and file path.

FOR EACH LOOP EDITOR

• In variable mapping, we select the created variable.

VARIABLE MAPPING

• Then after we goto the data flow and right-click on the flat file connection manager properties.

PROPERTIES

• In the property, we set the expression.

• In property select the Connection string and goto the Expression.

• In Expression drag and drop the variable.

• As well as Delay Validation set as TRUE. Then we run the package.

After running we check the table in the SQL Server.

TABLE

Start with MSBI video as mentioned below to learn topic practically in-depth: