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