WildCards in SSIS Collection {not include} name xlsx

Question

I have a process built in SSIS that loops through Excel files and Import data only from those that include name Report.

My UserVariable used as Expression is: *Report*.xlsx and it works perfectly fine. Now I am trying to build similar loop but only for files that DOES NOT include Report in file name.

Something like *<>Report*.xlsx

Is it possible?

Thanks for help!

Matt

Answer 1

In your loop, put a Script task before your first task. Connect those two with a line. Right click that line and set Constraint Options to expression. Your expression would look like this...

FINDSTRING(@var, "Report", 1) == 0

Where @var is the loop iterable.

Only files without "Report" inside will proceed to the next step.

Referencing this exact answer. SSIS Exclude certain files in Foreach Loop Container

Answer 2

Unfortunately, you 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)
   

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