Foreach Loop Container in SSIS


Description Earlier in SQL Server 2000, Data Transformation Service (DTS) was used to loop over data files of a given type present in a directory and to import them into the destination (In simple words, moving or copying files based on the file name from one directory to another) was very hard to perform. But with the release of SQL Server 2005, this has been solved by adding Foreach Loop Container. So, now you would be having an idea what this container performs and we will learn how it performs that.

Example Scenario :- We will create one Source folder containing some data files (say text files) in it. Then we will create a package and with the help of Foreach Loop Container, these files will be copied to another folder which will act as our Destination.


Step 1. Create a Source folder (I have created a folder named Chander) containing some text files and Create an empty Destination Folder where you want to copy these files.

Step 2. Create a package using Business Intelligence Development Studio (BIDS).

Step 3. Create a variable with the name Filename with Scope-Package, Data type-String and Value-Default. This variable will hold the name of the file that SSIS is working on during each iteration of the loop.Variable for Foreach loop container

Step 4. Next, drag a Foreach Loop Container onto the Control Flow and double click on the container to configure it. Go to Collection tab, and specify the following fields as:-

  • Select the option Foreach File Enumerator under Foreach Loop Editor.
  • In the Enumerator configuration group, Go to folder field and click Browse and set the folder property to the source folder that has data files in it.
  • Set the Files property to default i.e. < *.*>.

Collection Tab for Foreach loop container

Step 5. Go to Variable mapping tab, select the variable (in this example it’s Filename) you created earlier from the Variable dropdown box, and then accept the default of 0 for the index. Click OK to save the settings and return to the Control Flow tab in the Package Designer.

Variable tab for Foreach loop container

Step 7. Drag a File System Task into the container’s box. Double-click the new task to configure it in the editor that pops up as:-

  • Set the operation field to Copy file.
  • Select <New Connection> for the Destination Connection property. When the Connection Manager dialog opens, select Existing Folder (as we have created Destination folder already) and Browse the folder
  • Set the IsSourcePathVariable property field to True.
  • Set the Source Variable field to User::FileName.

Destination Connection for Foreach loop container

Step 8. We are now ready to strike the execute button to see how the files are transferred from one directory to another directory. During the execution of our package, we will see each file being picked up from the Source folder and getting copied to the Destination folder.

NOTE :-  If you had set the Overwrite Destination property to True in the File System Task, the file would be overwritten if there was a conflict of duplicate filenames.

Execute for Foreach loop container
With these we finish our post on Implementing Foreach Loop Container in SSIS. I hope you liked my article. Please share your feedback below as Comments.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.2/10 (51 votes cast)
VN:F [1.9.22_1171]
Rating: +37 (from 39 votes)
Foreach Loop Container in SSIS, 9.2 out of 10 based on 51 ratings
  • Pingback: Containers in SSIS | pHpRing()

  • Pingback: File System Task in SSIS | PhpRinG()

  • Ramesh Narayanan

    Hi Chander,
    Nice explanation, i could understood easily and clearly.
    Ramesh Narayanan

    • Thanks a lot Ramesh for the lovely comment.
      Follow us on Google plus for latest updates !!!

  • sanjiv

    nyc xplanation

  • sanjiv

    Very Helpfull article…

  • Lok

    Very nice article and clear explanation. I would like to request you explain about different types of enumerators with examples plzzz..

  • Yene

    Very useful

  • atul

    very use full

    • Hi Atul,
      Glad to see a positive feedback.
      Our team is putting hard efforts to make your experience a pleasurable one.
      Do Follow us on Google plus for latest updates 🙂

  • swati

    Easy understandable articles.
    Great job

  • vennkat

    easy understand articles thanks………..

  • vennkat

    Easy understandable articles.
    Great job

  • Manoharan

    it is very usefull to me

  • sunil kumar

    would u please explain the usages of the different types of enumerators in for each loop container.where should we can use which type with some examples.
    thank you.

  • Sireesha

    Hi Chander,
    Nice explanation, unstood clearly but i want to delete files in source folder after moving to destination. Could you please explain this scenario. Thanks in advance.


  • KhaJa Moinuddin

    Thank u………

  • KhaJa Moinuddin

    Mr Chander Sharma
    CAN U Explain All Types Of Enumunaters plz