3

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
David F
  • 265
  • 2
  • 14
  • 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 Nov 29 '17 at 19:06

1 Answers1

1

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
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Any similar reference on Including specific Sub Folders? – David F Nov 29 '17 at 21:15
  • @DavidF what method you want? script task or expression task? – Hadi Nov 29 '17 at 21:17
  • @DavidF in the first link, in the script task method, i used Linq to filter files from the List. Just replace the conditions. It is the same way. Just read the answers and you will get it – Hadi Nov 29 '17 at 21:19
  • @DavidF or in the script task you can loop over the list item using `For each strpath as String in lstFiles` and delete unwanted pathes – Hadi Nov 29 '17 at 21:24
  • I was looking for Script tasks. – David F Nov 29 '17 at 21:38
  • @DavidF the first link provided contains exactly what you are looking for, i will copy the script and make some editing – Hadi Nov 29 '17 at 21:39
  • @DavidF i added an example take a look. This will get all files from specific folder, and loop over them in the ForEach loop container – Hadi Nov 29 '17 at 21:44
  • No this is not converted well to c# – Hadi Dec 01 '17 at 16:11
  • 1
    Hadi, I have tried to modify your script example, but it continues to error. I cannot import System.Linq System.IO or System.Collections.Generic. However, I did add them as using. A couple of issues are with IO.Directory and SearchOption. They do not appear to be recognized. Would you please take a look at the modified code I embedded, and let me know your expert opinion as to why it may be erroring. I will provide the code in the following post. Much appreciated for any assistance you can provide. – David F Dec 01 '17 at 16:14
  • 1
    Public Sub Main() { lstFiles As New List =("t:\customers\subsystem"); IO.Directory.GetDirectories("T:\customers \subsystem","*.*", SearchOption.TopDirectoryOnly); strDirectory.Contains("New Orders"); lstFiles.AddRange(Directory.GetFiles("T:\customers \subsystem, "*.*", SearchOption.TopDirectoryOnly); Dts.Variables.Item("FilesList").Value = lstFiles; Dts.TaskResult = ScriptResults.Success; End Sub } – David F Dec 01 '17 at 16:16
  • @DavidF my script is a vb.net script not c#. Just add a new script task. And select its language as visual basic not c# from its properties – Hadi Dec 01 '17 at 16:38
  • I understand. Let me try again. – David F Dec 01 '17 at 16:48
  • Hadi, I am down to 2 errors, which not being familiar with VB, I don't know how to resolve. 1. strDirectory gives me options in a dropdown. Change to Directory, Change to IO.Directory, Change to System.IO.Directory. There are also selections for creating a method, property or field stub. 2. lstFiles has a dropdown for creating a method, property or field stub. I added System.Linq, System.IO, and System.Collections.Generic to the Imports region. Any assistance on which selections I should select are appreciated. – David F Dec 01 '17 at 20:00
  • Give me a moment and i will give u a reply – Hadi Dec 01 '17 at 20:05
  • 1
    Hadi, The update resolved. I guess the next question is what is next? I have checked the Exclude specific Sub Folders and WildCards in SSIS Collection {not include} name xlsx posts that you referenced. One reference using an expression task with a data flow task inside the Foreach Loop, while the other references a data flow task inside the loop. Which example should I follow? Any assistance is appreciated. – David F Dec 04 '17 at 15:54
  • @DavidF i am sorry. You should use the second approach `only data flow task inside the loop` – Hadi Dec 05 '17 at 08:19
  • 1
    That got it. Much appreciated. – David F Dec 05 '17 at 21:37