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:
- Create five SSIS variables from the SSIS menu, as mentioned in list below:
- 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.
- Click Edit Script... and add the following code:
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;
}
}
- Drag a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.
- Drag one more Foreach Loop inside File Type collection Foreach loop, double click inner for loop to pop up the property window.
- Drag a File system Task to archive file inside Inner Foreach Loop and double click it to pop up the property window.
- 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..