SSIS Script to Delete and Create Folders

Question

I have a loop that runs and creates a bunch of files per folder. The folders are uniquely named based on the order range, so they can change. And the folder must exist or the loop crashes.

I want to create a script that will delete all subfolders and files in each subfolder, from a root dir. ie

Root = C:\Output\
SubFolder = C:\Output\T1-500\
SubFolder = C:\Output\T501-1010\
SubFolder = C:\Output\T1011-3076\

Then have it create folders as needed on the fly. I tried:

public void Main()
    {
        // Deletes subfolders and files in the main folder
        EmptyFolder(Dts.Variables["User::FolderName"]);

        // Creates new folder on the fly
        if (Directory.Exists(Dts.Variables["User::FolderName"].Value = 0))
            Dts.TaskResult = Directory.CreateDirectory(Dts.Variables["User::FolderName"]);
    }


    private void EmptyFolder(DirectoryInfo directoryInfo)
    {
        foreach (FileInfo file in directoryInfo.GetFiles())
        {
            file.Delete();
        }

        foreach (DirectoryInfo subfolder in directoryInfo.GetDirectories())
        {
            EmptyFolder(subfolder);
        }
    }

It doesn't seem to pick up my package level variables, and won't let me add new ones.

I get the following when I try to use my folder variable:

To remove folders on the command line you can use RD <yourfolder> /S /Q. — Nick.McDermaid
What does "It doesn't seem to pick up my package level variables" mean? are you getting an error or are you unable to add them in the dialog? — Nick.McDermaid
Your variable is a string. That method doesn’t take a string. You need an extra step to convert it to a “DirectoryInfo” object. — Nick.McDermaid
i.e. this: stackoverflow.com/questions/18335754/…. Please Note in future please post the actual error, don't post things like "It doesn't seem to pick up my package level variables" - that doesn't mean anything. If you explain the full issue upfront it saves a lot of back and forth and half the time you solve it yourself anyway — Nick.McDermaid

Nick.McDermaid Nick.McDermaid · Accepted Answer · 2019-02-06T05:18:45

The error means you are passing in the wrong data type. Try this.

Instead of this:

EmptyFolder(Dts.Variables["User::FolderName"]);

Use this:

String folderName = (string)Dts.Variables["User::FolderName"].Value;
DirectoryInfo di = new DirectoryInfo(folderName);
EmptyFolder(di);

There is probably a way to put all of it on one line but start with that.

As mentioned in comments below, the (string) cast may not be necessary if you just use the Value property - please also try that.

You will probably also need to use your new folderName string variable in other parts of your code.

As I mentioned in the comments you can also just run a command line like this to remove a folder and all subfolders

RD <yourfolder> /S /Q

SSIS Script to Delete and Create Folders

1 Answers