Use SSIS Foreach loop to loop multiple folders

Question

I have one main folder, which contains 40+ folders. I have created an SSIS package that creates subfolders in 22 of the 40 folders. I want to use a Foreach Loop in my package to loop through only the 22 folders for my script to return the names and date/timestamp of new subfolders created.

I can currently loop through all 40 folders, but I have not been able to locate information on how to target specific folders.

Any suggestions on references are appreciated.

Why not generate a list of the paths you need using an initial C# script and store the results in a local dataset for the loop to iterate over? — Eric Hauenstein

Hadi Hadi · Accepted Answer · 2017-11-29T19:17:07

You can generate a list of specific folder using a Script Task, or you can use the for each loop with an expression task to achieve this, just follow my answers on:

Script Example:

Public Sub Main()
    Dim lstFiles As New Generic.List(Of String)

    'Assuming that C:\Temp is the main folder
    'And We want to include all subdirectories that contains "Report" word

    For Each strDirectory As String In IO.Directory.GetDirectories("C:\Temp", "*.*", IO.SearchOption.TopDirectoryOnly)

        If Not strDirectory.Contains("Report") Then Continue For

        lstFiles.AddRange(IO.Directory.GetFiles(strDirectory, "*.*", IO.SearchOption.TopDirectoryOnly)

Next


    Dts.Variables.Item("FilesList").Value = lstFiles

    Dts.TaskResult = ScriptResults.Success
End Sub

Use SSIS Foreach loop to loop multiple folders

1 Answers