Click here to Skip to main content
13,762,666 members
Click here to Skip to main content
Add your own
alternative version


5 bookmarked
Posted 28 Dec 2012
Licenced CPOL

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

, 24 Jan 2013
Rate this:
Please Sign up or sign in to vote.
How do I set FileSpec dynamically for looping files in a foreach loop container?


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. 


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:
  4. public void Main()
            Variables varCollection = null;
            string header = string.Empty;
            string message = string.Empty;
            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
  5. Drag a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.
  6. Drag one more Foreach Loop inside File Type collection Foreach loop, double click inner for loop to pop up the property window.
  7. Drag a File system Task to archive file inside Inner Foreach Loop and double click it to pop up the property window.
  8. 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.. 


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rajesh Manjarekar
Technical Lead
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionIncomplete Pin
Member 112954345-Feb-16 9:17
memberMember 112954345-Feb-16 9:17 
GeneralMy vote of 5 Pin
BihaniBrijesh31-Dec-12 10:13
memberBihaniBrijesh31-Dec-12 10:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.181112.3 | Last Updated 24 Jan 2013
Article Copyright 2012 by Rajesh Manjarekar
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid