Click here to Skip to main content
Click here to Skip to main content

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

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

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:
  4. 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
    }
  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.. 

License

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

Share

About the Author

Rajesh Manjarekar
Software Developer (Senior)
United States United States
No Biography provided
Follow on   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinmemberBihaniBrijesh31-Dec-12 10:13 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141220.1 | Last Updated 24 Jan 2013
Article Copyright 2012 by Rajesh Manjarekar
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid