Prompting for a folder in SSIS 2008 Script Task

Question

What I'm trying to do here would seem to be pretty simple. At the start of my SSIS package I want to set a variable to a directory that the user is prompted for. Here's my VB code:

Public Sub Main()
    Dim fldDialog As FolderBrowserDialog = New FolderBrowserDialog
    fldDialog.Description = "Select the folder..."
    fldDialog.ShowNewFolderButton = False
    fldDialog.RootFolder = Environment.SpecialFolder.MyDocuments
    If fldDialog.ShowDialog() = DialogResult.OK Then
        Dts.Variables("variable").Value = fldDialog.SelectedPath
        Dts.TaskResult = ScriptResults.Success
    Else
        MsgBox("You need to select a folder!", MsgBoxStyle.Exclamation, "Error!")
        Dts.TaskResult = ScriptResults.Failure
    End If
End Sub

Of course, I've got "variable" set as a "ReadWriteVariables" in the Script Task Editor, and the "Imports System.Windows.Forms" at the top of my VB file.

When I run the task it just sits there yellow (as if it's running), but never shows the dialog. There's never even an error, it just sits there. I can run the same code within a standard windows application project no problem.

Not sure what's going on here. On know one quirk of showing an OpenFileDialog is you have to set the ShowHelp property to True, so I'm wondering if there's another quirk to getting this to run. Google only mostly shows me an old problem where the folder tree is blank, but I'm not even getting the prompt. Any help would be much appreciated.

You're likely to have trouble with any kind of user input during the execution of a package. It would probably be better to let the user pass in the folder as a variable into the package (via the /SET command line option). — N West, Feb 24 '14 at 14:34

de_finn · Answer 1 · 2020-06-04T06:29:05.737

I know it's a little bit late but I came across this Problem and found a fix for this. (Using C#)
You need to use the "OpenFileDialog" instead of the "FolderBrowserDialog" and you need to set a few adjustments. Here is a sample code which opens the explorer and lets you pick a folder:

public void Main()
    {

        string myPath="";
        OpenFileDialog folderBrowser = new OpenFileDialog();
        folderBrowser.ValidateNames = false;
        folderBrowser.CheckFileExists = false;
        folderBrowser.CheckPathExists = true;
        folderBrowser.FileName = "Folder Selection.";
        folderBrowser.ShowHelp = true;
        if (folderBrowser.ShowDialog() == DialogResult.OK)
        {
            myPath = Path.GetDirectoryName(folderBrowser.FileName);
        }
        else
        {
            MessageBox.Show("Error selecting path");
        }
        Dts.Variables["User::varFolderPath"].Value = myPath;
        Dts.TaskResult = (int)ScriptResults.Success;
    }

The most important statement is the "folderBrowser.ShowHelp = true" statement. If this assignment isn't made you'll get the same problem as in your question.
You also need the statements above to "trick" the Dialog so you can select a Folder instead of a File.
I hope I can help you or people with the same problem but you should pass in the folder as a variable into the package as "N West" said.

Prompting for a folder in SSIS 2008 Script Task

1 Answers1