Foreach Loop in SSIS
- 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.
- In the SSIS firstly we drag and drop the data flow task.
- After that, we drag and drop the flat file source in the SSIS toolbox and configure the file.
- Right-click on the Flat File Source component → Goto Edit option → And in the Connection Manager we connect to a text file.
- For connecting text file click on the New and browse the text file.
- After that check the column in the Column option. Then click on the OK button.
- In the SSIS toolbox, we drag and drop ADO NET Destination for text file data store in the SQL Server.
- By configuring we right-click on ADO NET Destination → Goto Connection Manager →New
- Here enter the Server name and Database name and click on OK.
- Here configure the properties used to insert data into a destination using ADO.NET provider.
- After configuring the connection manager we check the Mappings. And click on OK.
- Select ForEach loop container from SSIS ToolBox.
- We Create a variable by right click on the white space area.
- 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.
- In variable mapping, we select the created variable.
- Then after we goto the data flow and right-click on the flat file connection manager 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.
Start with MSBI video as mentioned below to learn topic practically in-depth: