How do I prompt for input from an SSIS package?

Question

I want to be able to have a sql query in my DTSX package and I want to be able to have some sort of prompt to update the value of a null column. See what I have below:

UPDATE  SF1411
SET     [QuoteNumber]   = '123456'
    ,   [ItemNumber]    = '123654-100'
    ,   [DeleteItem]    = 'NO'
WHERE   [QuoteNumber]   = '0'

I want to be able to be prompted for the QuoteNumber and ItemNumber, then have the script update as needed. Is this possible and if so how can I do it?

Solution 1

This can be acheived as below: This will be in your intial script component.

    System.Windows.Forms.Form frm = new Form();
    TextBox txt = new TextBox();
    Button inputset = new Button();

    public void Main()
    {
        inputset.Text = "Set Variable Value";
        inputset.Width = 200;
        inputset.Height = 100;
        inputset.Click += new EventHandler(inputset_Click);
        txt.Name = "Input";
        frm.Controls.Add(txt);
        frm.Controls.Add(inputset);
        frm.ShowDialog();
        MessageBox.Show(Dts.Variables["Value1"].Value.ToString());


        Dts.TaskResult = (int)ScriptResults.Success;
    }

    void inputset_Click(object sender, EventArgs e)
    {
        Dts.Variables["Value1"].Value = Convert.ToInt32(txt.Text);
        frm.Close();
    }

This should be the initial component in your package to set the variable value or construct you SQL Command.

Solution 2

In general, an SSIS package is not used interactively. Your cleanest solution is a custom solution that gets the input from the user, and then launches the SSIS package.

A simpler alternative is using Package Configurations. You can store the user input in an external location (XML file, SQL Server database, and others) and the SSIS package will load the value at run time.