Unable to update a variable inside a Script Component in a data flow task

  • When out, the SSIS's variable theVariableToWriteTo is still empty.

    The code stop at: this.VariableDispenser.GetVariables(out varCollection); and the catch block doesn't catch anything (I've remove the MessageBox showing any error). The component turns green anyway.

    The class variable theVariableToWriteTo has a value inside the class.

    SS 2k8 R2

    Component ReadWriteVariables property: theVariableToWriteTo

    Yes the variable exist in the scope.

    The script code use C#

    public override void PostExecute()

    {

    base.PostExecute();

    IDTSVariables100 varCollection = null;

    try

    {

    this.VariableDispenser.LockForWrite("User::theVariableToWriteTo");

    this.VariableDispenser.GetVariables(out varCollection);

    varCollection["User::theVariableToWriteTo"].Value = this.theVariableToWriteTo.ToString();

    } catch (Exception e) {

    throw e;

    } finally {

    varCollection.Unlock();

    }

    }

    what am I doing wrong?

    Thank you

  • It's been a while, but I seem to remember that you do not need to have the variable listed in ReadWriteVariables if you are using the VariableDispenser methods to read and write - so try removing it and see what happens.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Hi Phil,

    It goes further and the script component seems to works ok.

    I've check the value inside the script component using:

    System.Windows.Forms.MessageBox.Show(varCollection["User::theVariableToWriteTo"].Value.ToString());

    and the value's right.

    But the variable at the package level is still empty once the component end.

    Any idea why?

  • May I ask what technique you are using to check the variable's value?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Sure,

    The next component, in the control flow, I've added a breakpoint on the "PreExecute" event and check the locals variables in the debugger.

    I've also do the samething a few component later just in case of, but same result.

  • Megistal (7/15/2013)


    Sure,

    The next component, in the control flow, I've added a breakpoint on the "PreExecute" event and check the locals variables in the debugger.

    I've also do the samething a few component later just in case of, but same result.

    Cool - that's how I would do it. Very odd - I will knock up a quick package and give it a try.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Oh my mistake, some variables have the same name but different scope so that might be my issue now.

    One of them is having the right value while the other's empty.

    I'll start from there.

    Thanks for your help!

  • Yep, I confirm that your method works for me - I would say that you have a scope issue.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply