SSIS mulitple variables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS mulitple variables

Author

Topic

jlb22
Starting Member

3 Posts

Posted - 2014-08-13 : 07:42:20

Hello, I would be grateful if anyone could help me. I have an SSIS package that prompts the user for a variable using the code below. All I want to do is for the form that pops up to have 20 fields, so when the button is clicked it assigns the value put in the second field to a second variable (Campcode2), the value in the third field to a third variable (Campcode3) etc etc...ie
Dts.Variables["CampCode"].Value = txt.Text.ToString();
Dts.Variables["CampCode2"].Value = txt.Text.ToString();
Dts.Variables["CampCode3"].Value = txt.Text.ToString();

If anyone can think of a simple way of doing this I would really appreciate it. Many thanks

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_21bd9709f1944de2a5f7eaf9faac94cd.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
System.Windows.Forms.Form frm = new Form();
TextBox txt = new TextBox();
Button inputset = new Button();
public void Main()
{
inputset.Text = "Enter Campaign Code";
inputset.Width = 100;
inputset.Height = 200;
inputset.Click += new EventHandler(inputset_Click);
txt.Name = "Input";
frm.Controls.Add(txt);
frm.Controls.Add(inputset);
frm.ShowDialog();

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

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

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 07:47:57

I think you are already using the simplest approach.

jlb22
Starting Member

3 Posts

Posted - 2014-08-13 : 09:05:08

Hello, thanks but I don't understand what I have to add to this code:
to add 19 more text boxes, and to assign the value from each text box to each variable when the button is clicked. I am good on SQL but a total beginner on C#. If anyone could tell me I would really appreciate it

System.Windows.Forms.Form frm = new Form();
TextBox txt = new TextBox();
Button inputset = new Button();
public void Main()
{
inputset.Text = "Enter Campaign Code";
inputset.Width = 100;
inputset.Height = 200;
inputset.Click += new EventHandler(inputset_Click);
txt.Name = "Input";
frm.Controls.Add(txt);
frm.Controls.Add(inputset);
frm.ShowDialog();

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 10:27:34

Yes, add 19 more text boxes! like this:


TextBox txt1 = new TextBox();
TextBox txt2 = new TextBox();
...
frm.Controls.Add(txt);
frm.Controls.Add(txt1);
...

jlb22
Starting Member

3 Posts

Posted - 2014-08-13 : 10:56:10

something like this? this just gives me one field and an elongated button called enter postcodes? Please help if you can

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

Button inputset = new Button();
public void Main()
{
inputset.Text = "Enter Postcodes";
inputset.Width = 100;
inputset.Height = 200;
inputset.Click += new EventHandler(inputset_Click);
txt.Name = "Input";
frm.Controls.Add(txt);
frm.Controls.Add(txt1);

frm.Controls.Add(inputset);
frm.ShowDialog();

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

void inputset_Click(object sender, EventArgs e)
{
Dts.Variables["Postcode1"].Value = txt.Text.ToString();
Dts.Variables["Postcode2"].Value = txt1.Text.ToString();
frm.Close();
}

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-13 : 12:26:09

I didn't suggest that you change your button at all. Perhaps just make it generic "submit" or something. The key is to add the missing input fields for the other 19 items. Also add Labels to them and add the Label before each TextBox.

If you want more, take a tutorial on Windows Forms -- there are lots of free ones to be googled.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources