SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS capturing multiple variables


SSIS capturing multiple variables

Author
Message
john.brees
john.brees
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
Hello, I would be grateful if anyone could help me. I have to modify an SSIS package that someone else has written 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...then I guess the void inputset_Click would contain:

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. I do not understand C#, I don't know what I have to put in the form code to create 19 new fields and then assign them to 19 new variables

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


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();
}
}
}
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8763 Visits: 7660
john.brees (8/14/2014)
Hello, I would be grateful if anyone could help me. I have to modify an SSIS package that someone else has written that prompts the user for a variable using the code below.

Wait, whut? This is quite possibly the worst use of SSIS you can do. SSIS is meant for automation behind the scenes and was never intended for user interfacing. This is why you're running into such significant problems trying to do it. It'll fight you every step of the way.

Yes, like everything else it can be done. You're opening up a can of worms you don't realize yet though if you try.

Are you willing to back this discussion up a bit? What is the purpose of this SSIS package and user interface? Maybe we can give you something a lot more friendly to attempt to implement.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 804
Any specific reason for having the UI. why don't you just put all the 20 variables in a confiuration file and pass those values at the runtime?
john.brees
john.brees
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
Thanks for the replies. It's running a sql stored procedure that has one argument. It's working well for the one argument, I just need to know how to modify the windows form. A configuration file is not going to be suitable, it's been in use for some time by an end user. I want to expand it to 20 variables, I just want it to store whatever is entered, if anything is entered.

I would really appreciate it if a visual C# expert could tell me how to modify the windows form to take 20 strings, then how to convert the strings to 20 variables. I know it is not ideal.

Many thanks
john.brees
john.brees
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
It is pulling customers out of a database according to the codes that are entered, the codes come over in a brief, there may be 1 or more than 1 but 20 should be sufficient
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 804


You also need to declare 20 variables named dtsCampaign_Code1,dtsCampaign_Code2 .....dtsCampaign_Code20 in the Variables window.


System.Windows.Forms.Form frm = new Form();
Label[] lblCampaignCode = new Label[20];
TextBox[] txtCampaignCode = new TextBox[20];
Button btnCampaignCode = new Button();
// Create a variables 'container' to store variables
Variables vars = null;


public void Main()
{

for (int i = 0; i < lblCampaignCode.Length; i++)
{

var lblCampaign_Code = new Label();
lblCampaignCode[i] = lblCampaign_Code;
lblCampaign_Code.Name = "lblCampaign_Code" + (i+1);
lblCampaign_Code.Text = "CampaignCode" + i+1;
lblCampaign_Code.Location = new Point(15, 32 + (i * 28));
lblCampaign_Code.Visible = true;
lblCampaign_Code.Font = new Font(lblCampaign_Code.Font, FontStyle.Bold);
lblCampaign_Code.TextAlign = ContentAlignment.MiddleCenter;
lblCampaign_Code.AutoSize = true;
frm.Controls.Add(lblCampaign_Code);
}




for (int i = 0; i < txtCampaignCode.Length; i++)
{

var txtCampaign_Code = new TextBox();
txtCampaignCode[i] = txtCampaign_Code;
txtCampaign_Code.Name = "txtCampaign_Code" + (i+1);
txtCampaign_Code.Location = new Point(172, 32 + (i * 28));
txtCampaign_Code.Visible = true;
txtCampaign_Code.Font = new Font(txtCampaign_Code.Font, FontStyle.Bold);
//txtCampaign_Code.TextAlign = ContentAlignment.MiddleCenter;
txtCampaign_Code.AutoSize = true;
frm.Controls.Add(txtCampaign_Code);
}

btnCampaignCode.Name = "btnCampaignCode";
btnCampaignCode.Text = "Submit";
btnCampaignCode.Visible = true;
btnCampaignCode.Font = new Font(btnCampaignCode.Font, FontStyle.Bold);
btnCampaignCode.AutoSize = true;
btnCampaignCode.Location = new Point(125, 32 + (23 * 28));
frm.Controls.Add(btnCampaignCode);
btnCampaignCode.Click += new EventHandler(btnCampaignCode_Click);





frm.WindowState = FormWindowState.Maximized;
frm.ShowDialog();


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

void btnCampaignCode_Click(object sender, EventArgs e)
{


foreach (TextBox inputBox in txtCampaignCode)
{

string variable = "dts" + inputBox.Name.Replace("txt", "");
// Lock variables
Dts.VariableDispenser.LockForWrite(variable);
// Add variables from the VariableDispenser to the variables 'container'
Dts.VariableDispenser.GetVariables(ref vars);

// Now you can use the variables
if (inputBox.Name.ToString().Substring(3, (inputBox.Name.ToString().Length) - 3) == variable.ToString().Substring(3, (variable.ToString().Length) - 3))
{

int number;
if( (int.TryParse(inputBox.Text, out number)) == true)
{
vars[variable].Value = Convert.ToInt32(inputBox.Text);
}
// if DTS varibale is of type String

// vars[variable].Value = inputBox.Text.ToString();

}
// Release the locks
vars.Unlock();

frm.Close();

}


}
}



john.brees
john.brees
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
rxm119528 thank you so much! I do appreciate your help, you are a great guy thanks again :-)
john.brees
john.brees
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
Thanks very much for this, I have tried using and it is only giving a couple of errors, the line:

Variables vars = null;

gives error:

Error 1 The type or namespace name 'Variables' could not be found (are you missing a using directive or an assembly reference?)

and:

Dts.VariableDispenser.GetVariables(ref vars);

gives error:

Error 2 The best overloaded method match for 'Microsoft.SqlServer.Dts.Runtime.VariableDispenser.GetVariables(ref Microsoft.SqlServer.Dts.Runtime.Variables)' has some invalid arguments st_c8fd8765cc1c4f55ba93d8a2aadcd90f
Error 3 Argument '1': cannot convert from 'ref Variables' to 'ref Microsoft.SqlServer.Dts.Runtime.Variables'

I have created the 20 variables in SSIS, dtsCampaign_Code1, dtsCampaign_Code2, dtsCampaign_Code3.....dtsCampaign_Code20 as string variables.

If anyone could help I would really appreciate it, I can see this is so close to working now!!

Many thanks
rxm119528
rxm119528
Mr or Mrs. 500
Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)Mr or Mrs. 500 (518 reputation)

Group: General Forum Members
Points: 518 Visits: 804
using Microsoft.SqlServer.Dts.Runtime

should be there in the namespace list.
john.brees
john.brees
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 35
Oh great thank you, that has removed those errors. The only errors I am getting now are whenever these 3 lines appear:

lblCampaign_Code.Location = new Point(15, 32 + (i * 28));
lblCampaign_Code.Font = new Font(lblCampaign_Code.Font, FontStyle.Bold);
lblCampaign_Code.TextAlign = ContentAlignment.MiddleCenter;

I get
Error 1 The type or namespace name 'Point' could not be found (are you missing a using directive or an assembly reference?)
Error 2 The type or namespace name 'Font' could not be found (are you missing a using directive or an assembly reference?)
Error 3 The name 'FontStyle' does not exist in the current context
Error 4 The name 'ContentAlignment' does not exist in the current context

Am I missing another reference?

thanks so much for help so far
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search