Unable to fetch “ReadWrite” Variable Value in Scri

Question

In Script Component [Input0_ProcessInputRow], i am trying to fetch "ReadWrite" global variable value and it throws below error.

ERROR:

The collection of variables locked for read and write access is not available outside of PostExecute.

Below is my code

If Row.Column13 = "C" Then
    Variables.mTotalCreditCount = Variables.mTotalCreditCount - 1
    Variables.mTotalCreditAmount = Variables.mTotalCreditAmount - CDbl(Row.Column14)

ElseIf Row.Column13 = "D" Then
    Variables.mTotalDebitCount = Variables.mTotalDebitCount - 1
    Variables.mTotalDebitAmount = Variables.mTotalDebitAmount - CDbl(Row.Column14)

End If

I also tried to read the value in local variable and then assign to global variable in the PostExecute() as below. No Luck

If Row.Column13 = "C" Then

    mTotalCrCnt = Variables.mTotalCreditCount - 1
    mTotalCrAmt = Variables.mTotalCreditAmount - CDbl(Row.Column14)
ElseIf Row.Column13 = "D" Then

    mTotalDbCnt = Variables.mTotalDebitCount
    mTotalDbCnt = mTotalDbCnt - 1
    mTotalDbAmt = Variables.mTotalDebitAmount
    mTotalDbAmt = mTotalDbAmt - CDbl(Row.Column14)
End If

Public Overrides Sub PostExecute()
    MyBase.PostExecute()
    Variables.ProcessCount = intProcessCount
    Variables.mTotalCreditCount = mTotalCrCnt
    Variables.mTotalCreditAmount = mTotalCrAmt
    Variables.mTotalDebitCount = mTotalDbCnt
    Variables.mTotalDebitAmount = mTotalDbAmt
End Sub

Any assistance please?

Answer 1

Looking to your comment it looks that you have solved the issue, but i am posting this answer to give information about how working with variables in a SSIS script and how to solve a similar issue, so it can helps other users

SSIS Variables

Variables Stores values that can be used in all SSIS components and containers.

Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables. More info in this MSDN article

Using Variables in Script components

Every script has a ReadOnlyVariables and ReadWriteVariables Lists that can be defined on the Script page.

ReadOnlyVariables

ReadOnlyVariables can be accessed from all script Sub's and they are Read-Only as they are named.

ReadWriteVariables

The collection of ReadWriteVariables is only available in the PostExecute method to maximize performance and minimize the risk of locking conflicts. Therefore you cannot directly increment the value of a package variable as you process each row of data. Increment the value of a local variable instead, and set the value of the package variable to the value of the local variable in the PostExecute method after all data has been processed. You can also use the VariableDispenser property to work around this limitation. However, writing directly to a package variable as each row is processed will negatively impact performance and increase the risk of locking conflicts. More in this MSDN article

Methods to Work with Variables

There are 3 Methods to work with variables:

1. Accessing them directly after having selected them as ReadOnlyVariables or ReadWriteVariables in the script page

2. Using a Variables Dispenser (LockForRead and LockForWrite methods)

   IDTSVariables100 vars = null;
   VariableDispenser.LockForRead("User::MyVariable");
   VariableDispenser.GetVariables(out vars);
   string TaskName = vars["User::MyVariable"].Value.ToString();
   vars.Unlock();
   

3. Using SSIS Logging Task: to read variable and log them to Execution Log, Message Box or File

There are many articles Talking about this methods and you can refer to them to learn more

• VariableDispenser.GetVariables Method (Variables)
• VariableDispenser.LockForWrite Method (String)
• 3 Ways -SSIS Read Write Variables – Script Task C# / VB.net
• Read and Write variables in a Script Component in SSIS (SQL Server Integration Services) using C#
• Use SSIS Variables and Parameters in a Script Task