Dynamic Folder SSIS

Question

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.

H:\x\xy2017\z\xy2017\a

H:\x\xy2018\z\xy2018\a

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.

        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());

        }

To restate, given a starting directory, I'd like to generate a list of all the folders that are named a specific value. — billinkc, Mar 21 '18 at 19:43
@billinkc yes, I am trying to have a list of folder paths that contain that specific folder name. Thanks — Hmen, Mar 21 '18 at 19:46
@dsdel yes I did but what that it doesn't seem to bring any results — Hmen, Mar 21 '18 at 21:40
@Hmen please update yoru post containing the code of what you tried — dsdel, Mar 22 '18 at 06:01
@Hmen is there a maximum depth of directories (as in each directory can at maximum 3 times contain your search string)? Does the drive contain many files? If not, perhaps finding all files is a better choice? — dsdel, Mar 22 '18 at 13:27
@dsdel yes the directories are gonna have the same level as specified in the example and there are a lot of other excel files in that root folder so I don't want to bring all the files. — Hmen, Mar 23 '18 at 20:36
@Hmem tried putting foreach in foreach? This way, you can search the received directory for all other directories below finding what you want — dsdel, Mar 23 '18 at 20:46

score 0 · Answer 1 · answered Mar 22 '18 at 04:39

0

Use the "Foreach Loop Container".

1 Go to "Collection" and choose the "Foreach File Enumerator".

2 It will ask for the folder that contains the files, here you have to choose your Main folder

ie in your case (H:\x).

3 Then you need to check the "Traverse subfolders" checkbox and that should do it.

answered Mar 22 '18 at 04:39

sandeep rawat

4,797
1
18
36

thank you but the naming of the excel files change as well and there are multiple excel files in the root. – Hmen Mar 22 '18 at 13:08
in files put *.xls or what ever extension .this will give all file in sub folder having extension.xls – sandeep rawat Mar 23 '18 at 02:42
Thanks for your input but there are a lot of excel files in that root folder I don't want so your way will also try to load those files and the package will fail. – Hmen Mar 23 '18 at 20:35

score 0 · Accepted Answer · answered Mar 27 '18 at 14:59

Here is the script task answer.

  string root = @"S:\directory\";
        string folder = "root folder";
        DateTime lookbackDate = DateTime.Now.AddMonths(-6);

        string messageText = "";

        messageText += String.Format("We're looking for files that have been made since {0}", lookbackDate);
        MessageBox.Show(messageText); messageText += "\n";
        messageText += ("Starting directory search now\n");
        MessageBox.Show(messageText); messageText += "\n";

        string[] dirs = Directory.GetDirectories(root, folder, SearchOption.AllDirectories);
        foreach (string dir in dirs)
        {
            messageText += String.Format("Directory found! {0}", dir);
            MessageBox.Show(messageText); messageText += "\n";

            foreach (string file in Directory.GetFiles(dir))
            {
                DateTime filedate = File.GetCreationTime(file);
                string fname = Path.GetFileName(file);

                if (filedate >= lookbackDate)
                {
                    messageText += String.Format("MATCH! {0} - created {1}", fname, filedate);
                    MessageBox.Show(messageText); messageText += "\n";
                }
                else
                {
                    messageText += String.Format("TOO OLD! {0} - created {1}", fname, filedate);
                    MessageBox.Show(messageText); messageText += "\n";
                }

            }
        }

        messageText += ("\nDirectory search complete!");
        MessageBox.Show(messageText);

Dynamic Folder SSIS

2 Answers2