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.