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.
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.
TABLE

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

➠鬼灭之刃 剧场版 无限列车篇 完整版本(2020-HD) Kimetsu no Yaiba: Mugen Ressha-Hen 完整版鬼灭之刃 剧场版 无限列车篇 Kimetsu no…

Minimum Initial Energy to Finish Tasks — Algorithms&Visualizations

Everything good and true about Agile I learned DJing

This post was written with wopa

Top 10 Managing And Monitoring tools for Windows

Implementing Dynamic Website Origin Routing While Providing a Seamless UI Experience Using…

TryHackMe — Tartarus CTF Writeup (Detailed)

The Wide World of Emoji

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vaishali Goilkar

Vaishali Goilkar

More from Medium

rest

“What Just Happened…”

What’s up “with” Diabetic Coding?

Shared reality