Exclude specific Sub Folders

Question

I got a package that runs through a folder and it's sub folders to get client data. The agreement has changed and now the client will post his data in different folder name every time. I was wondering if I can do a foreach loop on the main folder and exclude specific folders like archive .

I don't have knowledge in writing scripts so I was wondering if SSIS can do that without the script.

Answer 1

Using Execute Script Task

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 before 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 Directory as String = "C\Temp"
       Dim strSubDirectory as String = Directory & "\New Folder"
       Dim lstFiles As New List(Of String)
       lstFiles.AddRange(Directory.GetFiles(Directory, "*.*", SearchOption.TopDirectoryOnly).Where(Function(x) Not x.Contains(strSubDirectory)).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

Using Expression Task

For more details you can refer to my answer in the following link (it is a similar case) WildCards in SSIS Collection {not include} name xlsx