ForEach File Enumerator

It is a Container element in control flow group in the SSIS. The container iterats through the object collection it belongs to. The ForEach File Enumerator iterates all files and directories in a specifies directory. Let us wal through that with an example.

.Create a new package.
.Drag and Drop the Foreach Loop Container from the Toolbar.
.Right Click, Edit the Container Properties.
.In General tab, set the name of the container

.In Collection Tab, set the Enumerator to Foreach File Enumerator. Set the Folder path to the required path. the Files field can be used to filter the type of files. the file name retrieval format can be set. suppose if the specified contains sub folders and if we want to check inside those folders also then check the "Traverse subfolders" Check Box.
.In Variable Mappings tab, Create a new variable, for example "Var" with datatype as string.

also set the index to 0, as the collection manipulates with one object in a loop cycle.

.The Container is set and ready to use, let us check the container using script task.
.Place a Script Task inside the Foreach Loop Container.
.Right Click, Edit the task.
.Goto to Script tab, and set the ReadOnly Variable Field with the variable used in the Foreach Loop. here set to "Var".

.Click Design Script from the Script tab, a new VS window environment opens with pre set already.
.Type the content, as given below in the existing function
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
End Sub

.Save it, close the environment and click OK in the container Property box.
.The sript task will be evaluvated on every loop cycle. the loop object is passed as a read only parameter for script task. the main functioin in the task displays the value stored in the variable. In this way we can do file or manipulation kind of task through the SSIS. when the package is runed, the files in the specfied folder are displayed as message box to the end user.

No comments:

Post a Comment