SSIS in SQL Server 2012 Foreach Folder

Question

I've been battling against this issue for a while now and I don't seem to be making any progress and the few approaches I've read up on don't seem to work for me (for what ever reason).

I have a folder structure as follows:

--Main Folder
    --subfolder1
        --file1.txt
        --file2.txt
        --file3.txt
    --subfolder2
        --file4.txt
        --file5.txt
        --file6.txt

Using the ForEach File enumerator I am able to process each of the files in any given directory without any issues, however I cannot for the life of me work out how to loop through the directories.

I have a base path dir set as a variable. What I am trying to get to in SSIS is as follows:

foreach folder in mainfolder
do
    set variable to the folder name (i.e. subfolder1 (no path))
    foreach file in folder
    do
        Process File steps (can do this currently by specifying one of the sub folders)
    done
done

Hopefully that makes sense.

As I mentioned earlier, I have tried a couple of approaches including a script to build an XML blob with each folder path in it and then iterate through the nodes however that didn't work and I am sure there must be an easier way to do it.

To summarise, I need a foreach folder outer loop that sets a variable to the folder name so that I can nest my existing foreach file loop in it.

It might be that I am trying to do something that SSIS isn't able to do and I feel it should as I've done similar things in programming languages

Answer 1

A Foreach File Enumerator does not return directories. I was unaware of this until I started answering the question.

Set up

I created a series of folders an files under the base folder, much as you indicated

C:\SSISData\31081157
C:\SSISData\31081157\Subfolder1
C:\SSISData\31081157\Subfolder2
C:\SSISData\31081157\Subfolder1\file1.txt
C:\SSISData\31081157\Subfolder1\file2.txt
C:\SSISData\31081157\Subfolder1\file3.txt
C:\SSISData\31081157\Subfolder2\file4.txt
C:\SSISData\31081157\Subfolder2\file5.txt
C:\SSISData\31081157\Subfolder2\file6.txt

I built a package with 4 variables

• CurrentFile - string - Value does not matter
• CurrentFolder - string - Value does not matter
• FolderBase - string - Set this value to the base/root/parent folder for processing
• SubFolderList - Object

Round 1

My initial build out looked like

FELC Process Folders

I set this with a Directory of @[User::FolderBase] and left traverse subfolder unchecked. My Variable Mappings tab uses User::CurrentFolder as Index 0

FELC Process Files

I set this with a Directory of @[User::CurrentFolder] and my Variable Mappings tab uses User::CurrentFile as Index 0

SCR Emit

This is a script task that accepts two read only parameters: UserFolder and UserBase. I'm simply going to use this to show what the current values are by raising an OnInformation event (which shows up in your 'Progress' tab.

• ReadOnlyVariables: User::CurrentFile,User::CurrentFolder
• ReadWriteVariables:

The code is trivial

public void Main()
{
    bool fireAgain = false;
    string folderName = this.Dts.Variables["User::CurrentFolder"].Value.ToString();
    string fileName = this.Dts.Variables["User::CurrentFile"].Value.ToString();
    string message = string.Format("Folder = {0} : File = {1}", folderName, fileName);
    Dts.Events.FireInformation(0, "Emit", message, string.Empty, 0, ref fireAgain);

    Dts.TaskResult = (int)ScriptResults.Success;
}

But it never fires the inner foreach loop because there are no files in the outer loop. Oops

Round 2

This is probably how I'd solve this problem in the first place. Instead of figuring out all my directories first, I simply traverse the tree and then figure out where I am.

FELC Subfolder

I set this with a Directory of @[User::FolderBase] and left traverse subfolder checked. My Variable Mappings tab uses User::CurrentFile as Index 0

SCR Assign CurrentFolder

This is a script that will use the .NET libraries to find the containing folder

• ReadOnlyVariables: User::CurrentFile
• ReadWriteVariables: User::CurrentFolder

Code remains simple

public void Main()
{
    string currentFile = this.Dts.Variables["User::CurrentFile"].Value.ToString();
    string parent = System.IO.Directory.GetParent(currentFile).FullName;
    this.Dts.Variables["User::CurrentFolder"].Value = parent;
    Dts.TaskResult = (int)ScriptResults.Success;
}

SCR Emit

Same as above

Round 3

This assumes you must know the folder before you dive into it. In this case, we'll again go back to the .NET library to inspect the file system but now we will populate our last Variable User::SubFolderList with a list of the subfolders. Technically, it's an array of strings but no matter.

SCR Generate SubFolderList

A script task that uses the static method from Directory.GetDirectories to return our list of folders.

• ReadOnlyVariables: User::FolderBase
• ReadWriteVariables: User::SubFolderList

code is also simple

public void Main()
{
    string folder = this.Dts.Variables["User::FolderBase"].Value.ToString();
    this.Dts.Variables["User::SubFolderList"].Value = System.IO.Directory.GetDirectories(folder);

    Dts.TaskResult = (int)ScriptResults.Success;
}

FELC Process SubFolderList

This is a Foreach Enumerator but we must set the file type from File List to Foreach From Variable Enumerator. As your Variable, you'll use @[User::SubFolderList]. My Variable Mappings tab uses User::CurrentFolder as Index 0

FELC Process Files in CurrentFolder

Now that we know what CurrentFolder is, then we'll use a Foreach File Enumerator and specify the Directory of @[User::CurrentFolder]. I set my Variable Mappings tab uses User::CurrentFile as Index 0

SCR Emit

Same as above

Results

As you can see, I get the same results with approach 2 and 3. It just depends on what makes more sense for your specific use case.