Get list of subfolders in SSIS with SSIS Script Task!

Recently I needed to get a list of subfolders in SSIS. I also only wanted to bring back a list of subfolders that had the date appended to the end. So let’s walk through how to do this in Script Task using c#.

image

First we need to create two variables in SSIS:

image

Next bring a script task into the control flow and open it up for editing.

  • Select C# for the script language.
  • ReadWriteVariables select objDirectoryList

image

Now select Edit Script

  • Under the section “public void Main()” enter the following code:
    • Of course replace the directory location with your directory.
Dts.Variables["objDirectoryList"].Value = 
       System.IO.Directory.GetDirectories(@"C:\Blogs\RootFolder\","*20*",AllDirectories);
Dts.TaskResult = (int)ScriptResults.Success;
        }

        public SearchOption AllDirectories { get; set; }

Essentially all I’m doing here is populating the object variable objDirectoryList with all the subdirectories in the folder C:\Blogs\RootFolder\. Notice that I have also added a filter here “*20*”. If you do not wish to have a filter and  you want to bring back all subdirectories then just replace my filter with “*”.

Finally we can now iterate through this object variable in a FELC.

image

Below you can see the two directories that are returned from the script task above.

image

image

Thanks for looking, hope this helps.

Advertisements

4 thoughts on “Get list of subfolders in SSIS with SSIS Script Task!

  1. Sara March 9, 2017 / 4:03 pm

    Hi Mitchell, this is very useful article. After you get the list of sub-folders, do you know how to copy those folders to another location?
    Please help.

  2. Sara March 14, 2017 / 2:36 pm

    Hi Mitchell,

    Thank you for your reply. I used object variable and assigned it to for each loop container and used file system task to copy directory using the variable. It is copying the sub folders, but after copying sub folders it is trying to write the files and failing since the file already exists.

    Thanks,
    Radhika

  3. Sara March 14, 2017 / 3:08 pm

    Never mind, I got it.

    Thanks!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s