Dts.Variables not recognized inside Script Task?

  • 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/[/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."

  • 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.

  • Hi,

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

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

    :rolleyes:

  • 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."

  • 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."

  • 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

  • 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