SSIS: Archive or Copy Files using dynamically specifying FileSpec file types for File Enumerator in Foreach loop

Introduction

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

We need to read or archive different FileSpec or Extention Types of files using SSIS Package and send notification after successfully archived. 

Many people may have the question on how to dynamically set the FileSpec / File Type 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 decided to compose one. 

Background

For example, if file names starting with EOD*.xls and INTRADAY*.csv are in one folder and there are other files as well you want to archive only those files that we have mentioned and send notification to team once it's copied for them. 

Using the code

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

1. Create five SSIS variables from the SSIS menu, as mentioned in list below:



2. Drag a Script Task into the Control Flow panel, rename it to "Task To get filename which is going to be import", double click the task, and specify "User::gCollectionSearchFiles" into the ReadWriteVariables field.
3. Click Edit Script... and add the following code:
C#

public void Main()
{
    try
    {
        Variables varCollection = null;
        string header = string.Empty;
        string message = string.Empty;

        Dts.VariableDispenser.LockForWrite("User::gSearchFileNames");
        Dts.VariableDispenser.LockForWrite("User::gCollectionSearchFiles");
        Dts.VariableDispenser.GetVariables(ref varCollection);

        string[] strFileTypes = varCollection["User::gSearchFileNames"].Value.ToString().Split(',');

        varCollection["User::gCollectionSearchFiles"].Value = strFileTypes;
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception ex)
    {
        Dts.TaskResult = (int)ScriptResults.Failure;
    }
    // TODO: Add your code here
}

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





5. Drag one more Foreach Loop inside File Type collection Foreach loop, double click inner for loop to pop up the property window.





6. Drag a File system Task to archive file inside Inner Foreach Loop and double click it to pop up the property window.



7. Last task is to send email notification, drag Send Mail Task, and set From, To , Subject and  MessageSource property to send.

Here is what the package looks like..