WildCards in SSIS Collection {do not include} xlsx name

Question

I have a process built in SSIS that processes Excel files and only import data from those that include the name Report.

My UserVariable used as expression: *Report*.xlsx and it works fine. Now I'm trying to create a similar loop, but only for files that DO NOT include a report in the file name.

Something like *<>Report*.xlsx

Is it possible?

Thanks for the help!

Matt

Answer 1

Unfortunately, you cannot achieve this using the SSIS expression (something like *[^...]*.xlsx ), you need to find some workarounds:

Bypass

First

Get a list of filtered files using the Execute Script Task before entering Loop and loop over using the ForEach Loop container (Ado enumerator)

• You must have an SSIS variable (ex: User::FilesList ) of type System.Object (Scope: Package)
• Add an Execute Script Task for each Loop container and add User::FilesList as the ReadWrite Variable

• In Script Write, the following code:

  Imports System.Linq Import System.IO Import 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 

• In the container for each cycle, select the enumeration type as "From the Variable Enumerator" and select the FilesList variable as the source

Screen shots

Second

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

• Add a variable of type System.Boolean (Name: ExcludeFile)
• Inside the ForEach outline container, add the Expression Task component before you import the Excel file DataFlowTask

3. Inside the selection task, write the following:

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

4. Double-click on the connector between the expression task and DataFlowTask and write the following expression

    @[User::ExcludeFile] == False 

Note. There is no need to use the Expression Task to verify this, you can use the Dummy DataFlowTask or Script Task to check if the file name contains the keyword that you want to exclude or not

Answer 2

In your loop, put the Script task before the first task. Connect the two to the line. Right-click this line and set the Constraint Options expression. Your expression will look like this:

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

Where @var is a loop.

Only files without a “Report” inside will go to the next step.

Link to this exact answer. SSIS Exclude Specific Files in Foreach Path Container