title: Dynamic Folder SSIS
body: <p>I am creating a package that goes through a few folder paths and loads excel files. The file paths for the excel files are as follows. The files are located in "a" folder which is named the same from year to year, however, the "xy*" folder name changes depending on the year. So I want SSIS to look for the excel files by searching through the H drive that has the "a" folder. I looked at System.IO.Directory.GetDirectories(string, string,SearchOptions) but that only gives me the first subfolder. for instance H:\x\xy2017. any help is much appreciated. </p>
  
  <p>H:\x\xy2017\z\xy2017\a</p>
  
  <p>H:\x\xy2017\z\xy2017\a</p>
  
  <p>H:\x\xy2018\z\xy2018\a</p>
  
  <p>This is the C# code I used in Script task to recursively loop through the folders. I am not sure what I am doing wrong.</p>
  
  <pre><code>        Dts.Variables["User::varFolderPath1"].Value = Directory.GetFiles(@"S:\HEDIS", "*DMHM Lead Results*", SearchOption.AllDirectories);
  
          foreach (Object obj in Dts.Variables["User::varFolderPath1"].Value.ToString())
          {
  
              Console.WriteLine(Dts.Variables["User::varFolderPath1"].Value.ToString());
  
          }
  </code></pre>