Tuesday, October 23, 2012

For Each Loop Container in SSIS

It defines a repeating control flow in a package. Loop implementation in the for each loop container is similar to the ForEach loop concept in programming language.
Types of EnumeratorsForeach File Enumerator – This enumerate files in a folder. We can get list of files which has extension .jpg from any folder and its subfolders
Foreach Item Enumerator – Enumerate values in an item
Foreach ADO Enumerator – Enumerate rows in tables
Foreach ADO.Net Schema Rowset Enumerator – Enumerate a schema
Foreach from variable Enumerator – Enumerate the value in a variable
Foreach nodelist Enumerator – Enumerates nodes in an XML document
Foreach SMO Enumerator – Enumerate a SMO Object.



Foreach Loop Container Properties1. Enumerator – Select appropriate enumerator (select Foreach File Enumerator)
2. Folder – Select source folder from where you want to enumerate files
3. Files – Specify extension (*.xls)
4. Drag the script task inside the for each loop container
5. Bind the variable User::File_Name to ReadOnly Variable
6. Below code snippet displays list of files which are matched with .xls extension from the specified folder.


MsgBox(Dts.Variables("File_Name").Value)
Dts.TaskResult = ScriptResults.Success


No comments: