SSIS - Foreach Loop Exclude Files containing a specific word

Assuming that we want to loop over .xlsx and we want to exclude files containing the word "Report", so whe are searching for something like this in the Foreach Loop container  *<>Report*.xlsx

Unfortunately, we cannot achieve this using SSIS expression (something like *[^...]*.xlsx) you have to search for some workarounds:

Workarounds

First

Get List of - filtered - files using an Execute Script Task before entering Loop and loop over then using ForEach Loop container (Ado enumerator)

1. You have to a a SSIS variable (ex: User::FilesList) with type System.Object (Scope: Package)
2. Add an Execute Script Task befor the for each Loop container and add User::FilesList as a ReadWrite Variable
3. In the Script Write The following Code:
   Imports System.Linq Imports System.IO Imports System.Collections.Generic

   Public Sub Main()
       Dim lstFiles As New List(Of String)
       lstFiles.AddRange(Directory.GetFiles("C:\Temp", "*.xlsx", SearchOption.TopDirectoryOnly).Where(Function(x) Not x.Contains("Report")).ToList)
   
       Dts.Variables.Item("FilesList").Value = lstFiles
   
       Dts.TaskResult = ScriptResults.Success
   End Sub

4. In the For each Loop Container Choose the Enumertaion Type as 'From variable Enumerator' and choose FilesList variable as a source

ScreenShots

Second

Inside the for each loop add an Expression Task to check if the file contains Report string or not

1. Add a variable of type System.Boolean (Name: ExcludeFile)
2. Inside the ForEach Loop Container add an Expression Task component before the DataFlowTask you that imports the Excel File
3. 
   Inside The Expression Task write the following:

    @[User::ExcludeFile]  = (FINDSTRING(@[User::XlsxFile], "Report", 1 ) == 0)













Double Click on the connector between the expression task and the DataFlowTask and write the following expression

@[User::ExcludeFile] == False

Note: It is not necessary to use an Expression Task to validate this you can use a Dummy DataFlowTask or a Script Task to check if the filename contains the Keyword you want to exclude or not

This is a part of my Stackoverflow answer posted 6 Mar 2017