Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dts.Variables not recognized inside Script Task?


Dts.Variables not recognized inside Script Task?

Author
Message
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
I am attempting to check a variable User::tableDestination inside a Script Component, which is itself within a Data Flow task. I have set the ReadOnlyVariables to include User::tableDestination, and I am using the correct (I think) namespaces in the header of my C# script.

However, SSIS tells me that "The name 'Dts' does not exist in the current context", so I'm doing something wrong with the scope, assuming it's something brain-dead.

Every example I can find on using variables within script tasks tells me to use Dts.Variables("tableDestination").Value, so that leaves me a little confused.

http://msdn.microsoft.com/en-us/library/ms135941.aspx

http://blogs.msdn.com/b/benjones/archive/2009/03/29/using-a-c-script-task-in-ssis-to-download-a-file-over-http.aspx

http://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/

http://sqlblog.com/blogs/denis_gobo/archive/2007/07/04/1575.aspx


Any help appreciated. Screenshot attached

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Attachments
Dts variables not accessible.JPG (16 views, 123.00 KB)
Dan.Humphries
Dan.Humphries
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 1212
I use VB where I see you are using c# so it may be different butI I thought I would share my VB expeirence. In the control flow dts.variable works fine and I was ablt to call the variable with no problem. however in data flow task I had to do something quiet different. I had to us
Me.VariableDispenser as I said this may or may not be the case in c# but that worked for me in VB. I also had to lock the variable in the preexecute and the save that value to an internale value to be used during the rest of the processing. So in this example I am just reading the value of a previously read value and incrementing by one and then writting out to a row. Hope this helps you.

Public Class ScriptMain
Inherits UserComponent
Dim MaxRes As Integer
Public Overrides Sub PreExecute()
Dim var As IDTSVariables90
Me.VariableDispenser.LockOneForRead("MAXRESER", var)
MaxRes = CType(var("MAXRESER").Value, Integer)
MaxRes = MaxRes + 1
var.Unlock()
MyBase.PreExecute()
End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.PERSONSTATUSID = MaxRes
MaxRes = Maxres + 1
End Sub

End Class



Dan

If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
HakunaMatata
HakunaMatata
SSC Eights!
SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)SSC Eights! (918 reputation)

Group: General Forum Members
Points: 918 Visits: 491
Hi,

Me.VariableDispenser.LockOneForRead("MAXRESER", var) works for vb.

To use same in C#, just use this instead of me. :-)

Rolleyes
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
Thanks guys, I think that helped me get this one working, at least for this step. I'll post back if I run into other issues as I continue.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
Alright, I definitely don't know what I'm doing ;-)

I get values of 'System.__ComObject' for my variable instead of what I think I assigned.

In the script task from the Control Flow level I'm doing this to set it:
        public void Main()
{
// Create a logical file object
System.IO.FileInfo theFile = new System.IO.FileInfo(Dts.Variables["FileName"].Value.ToString());

// update the user variable FullFilePath
//Dts.Variables["FullFilePath"].Value = Dts.Variables["FullName"].Value.ToString();

// If the file exists and is a txt file, then set the appropriate tableDestination and processFile.
if (theFile.Exists && theFile.Extension ==".txt")
{
//MessageBox.Show("Processing file " + Dts.Variables["Filename"].Value.ToString());

if (theFile.Name.Contains("ADV_") && theFile.Name.Contains("2TO3"))
{
//MessageBox.Show("Processing file " + Dts.Variables["Filename"].Value.ToString());
Dts.Variables["ProcessFile"].Value = true;
Dts.Variables["tableDestination"].Value = "CPM_Recon_ODJFS_ADV_2TO3";
}



and in the script component within the data flow, I do this to read it:

 public override void Input0_ProcessInputRow(Input0Buffer Row)
{
IDTSVariables100 vars = null;
this.VariableDispenser.LockOneForRead("tableDestination", ref vars);
string strVars = vars.ToString();
vars.Unlock();

this.VariableDispenser.LockOneForRead("FileName", ref vars);
string fileVars = vars.ToString();
vars.Unlock();

try
{

//log which table is being processed
logBuffer.AddRow();
logBuffer.status = "Processing " + fileVars + " to " + strVars;

//try to process the file
if( strVars.Equals("CPM_Recon_ODJFS_ADV_2TO3"))
{
string str = Row.Column0.ToString();
ADV2TO3Buffer.AddRow();
ADV2TO3Buffer.type = "ADV2TO3";
ADV2TO3Buffer.MEMID = str.Substring(0, 12);
ADV2TO3Buffer.NUM = Convert.ToInt16(str.Substring(19, 1));
ADV2TO3Buffer.DENOM = Convert.ToInt16(str.Substring(27, 1));

logBuffer.AddRow();
logBuffer.status = "TRUE: ADV2To3";
}
else //if (true == false)
{
//do something else
logBuffer.AddRow();
logBuffer.status = "FALSE: Not ADV2To3";
}
}



It never equals the value I want, the "CPM_Recon_ODJFS_ADV_2TO3", so it returns "FALSE: Not ADV2To3" and "Processing System.__ComObject to System.__ComObject"

Is that an obvious fix to anybody?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
AndrewSQLDBA
AndrewSQLDBA
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 Visits: 3427
Set BreakPoints in your C# Script Editor, and verify the values in your variables. Step thru the code this way to troubleshoot issues like this. You can hover the mouse pointer over the variable and it will show you what the value is.

Andrew SQLDBA
the_joefish
the_joefish
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 6
To get the value of your variable and not "System.__ComObject"

Instead of:

string fileVars = vars.ToString();

Use:

string fileVars = vars["Filename"].Value.ToString();
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