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

Answer 2

you may have a more control, if you use Script task

Here is the sample code which I have used in one of SSIS:

 // Fetch Exclude Directory list from Table
        List<excludeDir> excludeDir = new List<excludeDir>();
        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\testDB.mdf;Integrated Security=True;User Instance=True");
        SqlCommand cmd = new SqlCommand("select DirList from excludeDir", conn);
        SqlDataReader dr;
        try
        {
            conn.Open();
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                excludeDir.Add(new excludeDir()
                {
                    Dir = dr.GetInt32(dr.GetOrdinal("DirList")),

                });

            }
            dr.Close();
        }
        catch (Exception exp)
        {

            throw;
        }
        finally
        {

            conn.Close();
        }
// compare against Sub directory list and process
string[] dirs = Directory.GetDirectories(@"C:\My Sample Path\");
string[] fileExclude = excludeDir ;
foreach (string path in dirs)
    {
        FileInfo f = new FileInfo(item2);

        listBox1.Items.Add(f.Name);

        for (int i = 0; i < fileExclude.Length; i++)
       {

           -- Console.WriteLine(fileArray[i]);

           IF dirs [i] == fileExclude [i]
           {
            //Set Flags accordingly and execute 
           }
       }

    }

Answer 3

You can't do this in the foreach loop properties, but what you can do is start the tasks inside the loop with a script task that checks to see if the folder name is a value that you want to exclude, and if it is, do nothing but loop to the next folder.

Answer 4

I would achieve this (without a Script Task) by setting the Disable property on the Tasks within the For Each Loop Container using an Expression, e.g.

FINDSTRING ( @[User::Each_File_Path] , "archive" , 1 ) > 0