Blog

Chander Sharma
Mar 15 th, 2013
SSIS16 Comments

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.

STEPS TO FOLLOW :-

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.4/10 (39 votes cast)
VN:F [1.9.22_1171]
Rating: +30 (from 32 votes)
Foreach Loop Container in SSIS, 9.4 out of 10 based on 39 ratings

16 Comments on this article

  • Ramesh Narayanan May 28, 2013

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

    Reply

    • Chander Sharma September 18, 2014

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

      Reply

  • sanjiv January 20, 2014

    nyc xplanation

    Reply

  • sanjiv January 20, 2014

    Very Helpfull article…

    Reply

  • Lok February 11, 2014

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

    Reply

  • Yene April 3, 2014

    Very useful

    Reply

  • atul September 15, 2014

    very use full
    thanks

    Reply

    • Chander Sharma September 18, 2014

      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 :)

      Reply

  • swati September 19, 2014

    Easy understandable articles.
    Great job

    Reply

  • vennkat November 4, 2014

    easy understand articles thanks………..

    Reply

  • vennkat November 4, 2014

    Easy understandable articles.
    Great job

    Reply

  • Manoharan November 27, 2014

    it is very usefull to me

    Reply

  • sunil kumar December 1, 2014

    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.

    Reply

  • Sireesha February 10, 2015

    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.

    -sireesha

    Reply

Add a comment