Exclude specific subfolders

Question

I have a package that goes through a folder, and subfolders - to receive client data. The agreement has changed, and now the client will publish his data in different folders every time. I was wondering if I can do a foreach loop in the main folder and exclude certain folders like archive .

I have no knowledge of scripting, so I was wondering if SSIS would be able to do this without a script.

Answer 1

Using Execute Script Task

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 (for example: User::FilesList ) of type System.Object (Scope: Package)
• Add an Execute Script Task in front of each Loop container and add User::FilesList as the ReadWrite variable

• In 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 

• In the container for each path, select the Enumertaion type as From variable Enumerator and select the FilesList variable as the source

Screen shots

Using the Expression Task

For more information, you can refer to my answer at the following link (this is a similar case) WildCards in the SSIS collection {not include} name xlsx

Answer 2

you can have more control if you use the Script task

Here is an example of the code that I 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 cannot do this in the properties of the foreach loop, but what you can do is run the tasks inside the loop using a script task that checks if the folder name is the value you want to exclude, and if so, do not nothing but a loop to the next folder.

Answer 4

I would achieve this (without the Script task) by setting the Disable property to Tasks in the Container for each loop using Expression, for example

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