SSIS: Dynamically specify folder path for Foreach File Enumerator in Foreach Loop Container

How do I set folder path dynamically for looping files in a foreach loop container?

Many people may have the question on how to dynamically set the folder path for Foreach File Enumerator in a Foreach Loop Editor. Surprisingly I do not find a blog/article talking about this issue clearly from internet search. So I decide to compose one.

It is quite easy to implement the feature if you know the property Directory of the Foreach File Enumerator. Now let us look at the steps:

1. Create two SSIS variables from the SSIS menu, saying vFileName and vFolderName.

 

2. Drag a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.

3. Switch to the Collection tab, choose the Enumerator as "Foreach File Enumerator", expand Expressions and add two properties "Directory" for folder path and "FileSpec" for filtering specific types of files. Specify "@[User::vFolderName]" to Directory and "*.txt" to FileSpec.

 

 

4. Switch to the Variable Mappings and specify User::vFileName mapping to the index 0.

 

 

5. Click OK. To show you a sample how it will work, I further add two Script tasks one for pre-setting the folder path while the other output the text files in it.

6. Drag a Script Task into the Control Flow panel, rename it to "Set Folder Path Task", double click the task and specify "User::vFolderName" into the ReadWriteVariables field.

7. Click Edit Script... and add the following code:

       public void Main()

        {

            // TODO: Add your code here

            Dts.Variables["vFolderName"].Value = @"D:\Test\SSIS Foreach Files";

 

            Dts.TaskResult = (int)ScriptResults.Success;

    }

8. Connect the script task to the Foreach Loop Container and drag a new Script Task into the container, and rename the new script task as "Output file name task".

9. Double click the "Output file name task", specify "User::vFileName" to the ReadOnly Variables and click the "Edit Script..." button.

10. Add the following code to output the files names:

        public void Main()

        {

            // TODO: Add your code here

            bool fireAgain = false;

            Dts.Events.FireInformation(0, "", Dts.Variables["vFileName"].Value.ToString(), "", 0, ref fireAgain);

            Dts.TaskResult = (int)ScriptResults.Success;

        }