Unable to fetch "ReadWrite" Variable Value in Script Component of SSIS

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?

Legatee

I tried this and able to access now. #13450789 – Legatee

#13450789 – Legatee

i got a similar issue but didn't know how to fix it? you provided a link but i didn't accepted the answer on this page and they are similar!! can you give us an answer? or accept the provided answer if it is what you tried? – Challenge

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

Valuer

Hadi, I edited your code because it didn't build. BTW, regarding step 1, the ReadWriteVariables can only be directly accessed in the PostExecute method, which is the problem here. Did you mean something else? – Kink

@Kink thanks for editing the code. This answer is posted after the OP solved his issue. It is to give additional information about how to use variables inside scripts, because the OP didn't provided any information. – Valuer