The Problem

The SSIS Foreach Loop component has a limitation when it comes to selecting different sets of files from a folder, for example, it can be easily configured to loop through TXT files or PDF files or even all types of files by setting the Files parameter equal to "*.TXT", "*.PDF", "*.*" respectively, but what if we want to look for TXT files and PDF files at the same time and skip all other types of files?  There is no option available to set the Files parameter to "*.TXT","*.PDF", however that can be achieved by creating a small script in the SSIS package to update a variable that can be referenced by the Foreach Loop container.

The following image shows my Source and Target folders before executing the Foreach Loop:

The Solution

This is an example for copying Excel files and TXT files but can be used for any other type of file as well and for any number of files. This can also be used (with very small modifications) for moving or deleting files:

First, create the variables listed below:

• FileFilters=”*.txt|*.xlsx” (pipe delimit all different file types that need to be copied)
• FileList= System.Object
• SourceFolder=”C:\Source”
• TargetFolder=”C:\Target”
• FileName (will get value dynamically inside of the Foreach loop container)

Update the SSIS package as follows:

"ST-Multiple file types load" is the name of the script to be executed, the ForEach Loop is named "EACH-Copy files" and "FILE-Copy File" is the actual File System Task responsible for copying the files.

Configure all three components per screenshots below, the code for the script task is also listed:

The Script task code:

public void Main()
{
  string DataFolder, FileFilters;
  DataFolder =Dts.Variables["User::SourceFolder"].Value.ToString();
  FileFilters = Dts.Variables["User::FileFilters"].Value.ToString();
  ArrayList LookupFiles = new ArrayList();
  string[] strFileFilters = FileFilters.Split('|');
  // for each filter find matching file names
  foreach (string FileFilter in strFileFilters)
  {
    LookupFiles.AddRange(Directory.GetFiles(DataFolder,
    FileFilter, System.IO.SearchOption.AllDirectories));
  }
  Dts.Variables["User::FileList"].Value = LookupFiles;
  Dts.TaskResult = (int)ScriptResults.Success;
}

If we want to move files instead of copying files, then the parameter Operation should be set to "Move file", likewise if we want to delete files we set the parameter Operation equal to "Delete file".

The following image shows my folders after executing the Foreach Loop to get files copied from the Source folder to the Target folder:

Summary

In this article, we modified the standard behavior of the Foreach Loop container to iterate through a set of specific file types in a folder and copy them one-at-a-time to another folder. To make that possible, we created a script  to read a filter value and return an object with all of the files that match the filter (in our example, we were looking for files with extension *.txt" and *.xlsx"), the Foreach Loop used the object from the script to determine what files should be included or not in the copy operation. Similar approach can also be implemented for other operations such as moving or deleting files.