Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dts.Variables not recognized inside Script Task? Expand / Collapse
Author
Message
Posted Tuesday, September 21, 2010 8:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:08 PM
Points: 1,328, Visits: 19,288
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."


  Post Attachments 
Dts variables not accessible.JPG (12 views, 123.01 KB)
Post #990363
Posted Tuesday, September 21, 2010 9:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 10:26 AM
Points: 822, Visits: 1,203
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.
Post #990453
Posted Wednesday, September 22, 2010 10:04 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:55 PM
Points: 918, Visits: 491
Hi,

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

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


Post #991325
Posted Wednesday, September 22, 2010 10:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:08 PM
Points: 1,328, Visits: 19,288
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."
Post #991348
Posted Thursday, September 23, 2010 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:08 PM
Points: 1,328, Visits: 19,288
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."
Post #992011
Posted Thursday, September 23, 2010 4:00 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:32 PM
Points: 977, Visits: 3,357
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
Post #992392
Posted Tuesday, May 10, 2011 9:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 20, 2011 8:42 AM
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();
Post #1106242
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse