When working with SQL Server Integration Services (SSIS), you may come across a limitation in the Foreach Loop component. This limitation arises when you need to select different sets of files from a folder, such as TXT files and PDF files, while skipping all other types of files. Fortunately, there is a solution to this problem that involves creating a small script within the SSIS package.

To begin, let’s take a look at the problem. The Foreach Loop component allows you to loop through files by setting the Files parameter to a specific file type, such as “*.TXT” or “*.PDF”. However, there is no option available to select multiple file types simultaneously.

To overcome this limitation, we can create a script in the SSIS package that updates a variable, which can then be referenced by the Foreach Loop container. This script will allow us to select and process multiple file types at the same time.

Here is an example of how to copy Excel files and TXT files using this approach:

FileFilters = "*.txt|*.xlsx"
SourceFolder = "C:\Source"
TargetFolder = "C:\Target"

By delimiting the different file types with a pipe symbol (|), we can specify which file types should be copied. This approach can be used for any number of file types and can also be modified for moving or deleting files.

Let’s take a closer look at the steps involved in implementing this solution:

1. Create the following variables in the SSIS package:
• FileFilters: This variable should contain the file types you want to select, delimited by a pipe symbol (|).
• FileList: This variable will store the list of files that match the specified file types.
• SourceFolder: This variable should contain the path to the source folder.
• TargetFolder: This variable should contain the path to the target folder.
• FileName: This variable will dynamically receive the value inside the Foreach Loop container.
2. Update the SSIS package as follows:
• Create a script task named “ST-Multiple file types load” that will be executed.
• Create a Foreach Loop container named “EACH-Copy files” to iterate through the files.
• Create a File System Task named “FILE-Copy File” to copy the files.
3. Configure all three components according to the provided screenshots.
4. Implement the following code in the script task:

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 you want to move files instead of copying them, set the parameter “Operation” to “Move file”. Similarly, if you want to delete files, set the parameter “Operation” to “Delete file”.

After executing the Foreach Loop, you will find that the selected files have been copied from the source folder to the target folder.

In summary, we have explored how to modify the behavior of the Foreach Loop container in SSIS to iterate through a set of specific file types in a folder and perform operations such as copying, moving, or deleting them. By creating a script that reads a filter value and returns an object with all the files that match the filter, we can customize the behavior of the Foreach Loop to suit our needs.

This approach can be applied to various scenarios and is not limited to copying files. With minor modifications, you can use this technique for other operations as well.

Thank you for reading this blog post on working with multiple file types in the SSIS Foreach Loop. We hope you found it informative and helpful in your SQL Server projects.