September 21, 2010 at 8:43 am
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://www.simple-talk.com/sql/ssis/passing-variables-to-and-from-an-ssis-task/[/url]
http://sqlblog.com/blogs/denis_gobo/archive/2007/07/04/1575.aspx
Any help appreciated. Screenshot attached
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
September 21, 2010 at 9:37 am
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.
September 22, 2010 at 10:04 am
Hi,
Me.VariableDispenser.LockOneForRead("MAXRESER", var) works for vb.
To use same in C#, just use this instead of me. 🙂
:rolleyes:
September 22, 2010 at 10:24 am
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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
September 23, 2010 at 7:37 am
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[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
September 23, 2010 at 4:00 pm
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
May 10, 2011 at 9:33 am
To get the value of your variable and not "System.__ComObject"
Instead of:
string fileVars = vars.ToString();
Use:
string fileVars = vars["Filename"].Value.ToString();
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply