Update and use a Variable in an expression in SSIS

Question

I am trying to use a variable that I am updating in a loop inside an expression in SSIS. I am going to trying to be as clear as possible. Before to arrive to the last Data Flow Task (where I have a ODBC source), I am getting some values inside the variable (Return) that I am going to use in the [ODBC Source].[SqlCommand], with the second Script Task I could checked that the variable is been updating in the way I need. The problem is that in the Data Flow the expression is taking into account this variable but with the Default value that I have choosen ('').

enter image description here

In the first Script task I am updating the variable:

Dts.Variables["Return"].Value = Dts.Variables["Return"].Value + identif;

So, I do not know if I missed a previous indication or what could be happening with the execution of this control flow

Are you basing your assessment of @[User::Return] not being set by the above screenshot? — billinkc

Troy Witthoeft Troy Witthoeft · Accepted Answer · 2015-05-28T20:12:32

The Variables window will not show you the value of the variable at the end of package execution. You need to set breakpoints and use the local window to "catch" the value of the variable before and after it changes.

Breakpoints Example

I have a package with a single script task.

enter image description here

The code inside is this...

    new string strReturn = "NewValue";
    public void Main()
    {
        // TODO: Add your code here
        Dts.Variables["Return"].Value = strReturn;
        Dts.TaskResult = (int)ScriptResults.Success;
    }

The code sets the my SSIS variable equal to NewValue. To catch this variable changing values I will set breakpoints. To set breakpoints, right-click the Script Task and select Edit Breakpoints.

The PreExecute breakpoint will pause execution before the code is executed. The PostExecute will pause execution after the code is executed

Set a Pre and PostExecute breakpoint. Save and then execute the package. When it reaches the breakpoint, it will pause. The first pause is PreExecute event, just before the script is about to execute. During this pause, you can enter the name of your variable into the locals window.

enter image description here

You will see that the value has not changed yet. Press the continue button to move to the next breakpoint, PostExecute. Now, the Script Task has been executed. Check the local window and you should see that the value has changed!

enter image description here

Notice that throughout each of these the top left Variables window never shows an updated value. It always shows '' . You must use breakpoints to see a variable value changing. Hope this helps you to debug your package!

Update and use a Variable in an expression in SSIS

1 Answers