File System Task with Sequence and ForEach Loop Containers in SSIS

File System Task targets to create, copy, delete or move files/directories from the file system. Simply to say it handles below operations on files and directories in the file system. 



Here are few steps defined to move a set of text files from a source folder to a destination folder.

1.  Create an Integration Services Project in SQL Server Data Tools. Select the package and place a "Sequence Container" control and within that a "Foreach Loop Container" control and finally a "File System Task" inside that on "Control Flow" tab. 

• For Each Loop Container is used so as to loop through all files within the specified directory.
• Sequence Container is in place to contain multiple control flow arising from ForEach Loop and File System Task. It is similar to a package containing multiple data flow and tasks.

2. Double click on For Each Loop container and in the editor that appears, select the Enumerator type as "ForEach File Enumerator". 

• Select the source folder for searching with the files.
• Select File Type, if specific in search
• Select fully qualified name of the file for its destination.

3. Create a variable and map the source path to that variable as follows:

4. Double click on File System Task and select New Connection for destination.

5. Choose usage type as "Existing folder" and select the folder path.

6. Set OverwriteDestination to true and File Operation as "Move File". Select source path from pre-defined variable.

7. Click ok button in the above screen and execute the package and finally the result is that the files are moved from Source to Destination folder as shown below:

Cheers,

Venkatesan Prabu .J

Managing Director – KaaShiv Info Tech / MSP-MVP Mentor

www.kaashivinfotech.com