Foreach folder enumerator

Foreach folder enumerator

Case:
I want a Foreach Folder Enumerator, but the Foreach Loop component only loops through files.

Solution:
Unfortunately this isn't supported by the standard Foreach Loop component, but there are a couple of workarounds:
- Use an open source component Directory Foreach Enumerator or my own Custom Foreach Folder Enumerator
- Use a Script task to accomplish this.

This example uses the Script task and a Foreach Loop to enumerate through the folders. The Script tasks generates a list of Directories and the Foreach Loop loops through that list.

1) Control Flow
Drag a Script task and a Foreach Loop container to the Control Flow like the image below.

Control Flow

2) Variables
Create three string variables:

1. startlocation that indicates the parent folder wherein we gonna search for subfolders. Fill it with a path like "d:\foreachfoldertest\".
2. xmldoc which we gonna use for communicating between the Script task and the Foreach Loop.
3. folder which is used for the variable mapping in the Foreach Loop container.

Varibles (right click in Control Flow)

3) Script Task
Select the startlocation as a readonly variable (we only read the path inside it) and select xmldoc as a readwritevariable (an xml string will be stored in it).

Variables

4) The Script
Copy the following script to your Script task

// C# code // This script loops through a folder // and adds all subfolders to an xml string using System; using System.Data; using System.IO;        // Added using System.Xml;       // Added using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms;   namespace ST_5da96344f1c4411ab56207579f2e5e91.csproj {     [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]     public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase     {           #region VSTA generated code         enum ScriptResults         {             Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,             Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure         };         #endregion           public void Main()         {             // Get all subfolders within the parent folder             string[] subFolders = Directory.GetDirectories(Dts.Variables["User::startlocation"].Value.ToString());               // Create variables for the XML string             XmlDocument xmldoc = new XmlDocument();             XmlElement xmlRootElem;             XmlElement xmlChildElem;             XmlText xmltext;               // Add the root element: <ROOT>             xmlRootElem = xmldoc.CreateElement("", "ROOT", "");               // Variable for directory information             DirectoryInfo directoryInfo;               // Loop through the parent folder             foreach (string currentFolder in subFolders)             {                 // Fill directoryInfo variable with folder information                 directoryInfo = new DirectoryInfo(currentFolder);                   // Create the child element that contains the path:                 // <Folder>d:\foreachfoldertest\subfolder1\</Folder>                 xmlChildElem = xmldoc.CreateElement("", "Folder", "");                 xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);                 xmlChildElem.AppendChild(xmltext);                   // Add the child element to the root element                 xmlRootElem.AppendChild(xmlChildElem);             }             // Add the root element to the xml document             xmldoc.AppendChild(xmlRootElem);                           // Store the xml in the SSIS variable             Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();               Dts.TaskResult = (int)ScriptResults.Success;         }     } }

This script will result in:
<ROOT>
  <Folder>d:\foreachfoldertest\subfolder1</Folder>
  <Folder>d:\foreachfoldertest\subfolder2</Folder>
  <Folder>d:\foreachfoldertest\subfolder3</Folder>
</ROOT>

5) Foreach Loop Container
Edit the Foreach Loop Container and change the enumerator on the Collection tab to Foreach NodeList Enumerator. This means that it will loop through an xml string. Change the DocumentSourceType to Variable and select the xmldoc variable as the DocumentSource. This is the variable that contains the xml string from the Script task. Last... add the following string to the OuterXPathString: "/ROOT/*" (without the quotes). This means that it's gonna look within the root element.

Foreach NodeList Enumerator

6) Variable Mappings
Map the variable folder to Index 0. This will store the path in this variable.

Variable Mappings

7) Test the result
Now you can add your own tasks to the Foreach Loop container and use the variable folder to get the path. I added a simple Script Task for testing that shows the path in a MessageBox.

Example with MessageBox

8) More advanced script
You can even add a Traverse subfolders option with this recursive method.

// C# code // This script loops through a folder // and adds all subfolders to an xml string using System; using System.Data; using System.IO;        // Added using System.Xml;       // Added using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms;   namespace ST_5da96344f1c4411ab56207579f2e5e91.csproj {     [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]     public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase     {           #region VSTA generated code         enum ScriptResults         {             Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,             Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure         };         #endregion           // Variables for the xml string         private XmlDocument xmldoc;         private XmlElement xmlRootElem;           public void Main()         {             // Inialize XMLdoc             xmldoc = new XmlDocument();               // Add the root element: <ROOT>             xmlRootElem = xmldoc.CreateElement("", "ROOT", "");               // Add Subfolders as Child elements to the root element             GetSubFolders(Dts.Variables["User::startlocation"].Value.ToString());               // Add root element to XMLdoc             xmldoc.AppendChild(xmlRootElem);               // Fill SSIS variable with XMLdoc             Dts.Variables["xmldoc"].Value = xmldoc.InnerXml.ToString();               Dts.TaskResult = (int)ScriptResults.Success;         }           // Recursive method that loops through subfolders         private void GetSubFolders(String parentFolder)         {             // Get subfolders of the parent folder             string[] subFolders = Directory.GetDirectories(parentFolder);               // XML child element             XmlElement xmlChildElem;             XmlText xmltext;               // Variable for file information             DirectoryInfo directoryInfo;               // Loop through subfolders             foreach (string currentFolder in subFolders)             {                 // Fill fileInfo variable with file information                 directoryInfo = new DirectoryInfo(currentFolder);                   // Create child element "Folder":                 // <Folder>d:\foreachfoldertest\subfolder1\</Folder>                 xmlChildElem = xmldoc.CreateElement("", "Folder", "");                 xmltext = xmldoc.CreateTextNode(directoryInfo.FullName);                 xmlChildElem.AppendChild(xmltext);                                   // Add child element to root element                 xmlRootElem.AppendChild(xmlChildElem);                   // Recursive call                 GetSubFolders(directoryInfo.FullName);             }         }     } }

Note: there are more options to accomplish the communication between the Script Task and the Foreach Loop container, but I wanted you to show an infrequently used option of the Foreach Loop container. Let me know what your solution was.