Right not a problem, I just set up an SSIS For Each loop container click on ‘Traverse subfolders’ option and then set out the data connections.

No, sadly the ‘Traverse sub folder doesn’t work like that. Nuts, so after a quick search I found this link at Joost van Rossum’s blog that uses a bit of C# to get the folder list and generate an XML schema with the folder list in it. You can then use the ‘Foreach NodeList Enumerator’ in the Foreach Loop to get the files.

Well sort of, the code on that website only gets the folder structure, not the full path of the file. It was however a good starting point, and looked like it could be adapted to get the full file list, that could be passed on to the data flow logic in the Foreach Container. Now I’m TSQL through and through, my C# is poor, but slowly getting better, however I did mange to hack the code so it got the file list. So here it is, please use, improve and share.

#region Namespaces
using System;
using System.Data;
using System.IO;
using System.Xml;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
#endregion

namespace ST_b87894259c434eeca3da339009a06fdf
{
    /// 

    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// 

 
 // Use this for SQL Server 2012 and above
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
 // Use the below for SQL Server 2008, comment out the above
 //  [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: 
            xmlRootElem = xmldoc.CreateElement("", "ROOT", "");

            // Add Subfolders as Child elements to the root element
            GetSubFolders(Dts.Variables["User::FeedsFilePath"].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);

            var allfiles = DirSearch(parentFolder);

            foreach (var filePath in allfiles)
            {
                XmlElement xmlChildElem;
                XmlText xmltext;

              //  var directoryInfo = new DirectoryInfo(Path.GetDirectoryName(filePath));

                // Create child element "Folder":
                // d:\foreachfoldertest\subfolder1\
                xmlChildElem = xmldoc.CreateElement("", "File", "");
                xmltext = xmldoc.CreateTextNode(filePath);
                xmlChildElem.AppendChild(xmltext);

                // Add child element to root element
                xmlRootElem.AppendChild(xmlChildElem);
            }
   
        }
  // This bit gets the file list and adds it to the subfolders
        private List DirSearch(string sDir)
        {
            List files = new List();
          
                foreach (string f in Directory.GetFiles(sDir))
                {
                    files.Add(f);
                }
                foreach (string d in Directory.GetDirectories(sDir))
                {
                    files.AddRange(DirSearch(d));
                }
            return files;
        }
    }
}