sql-server - 无法在 SSIS 的脚本组件中获取 "ReadWrite"变量值

Question

在脚本组件 [Input0_ProcessInputRow] 中，我尝试获取“ReadWrite”全局变量值，但它抛出以下错误。

错误:

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

下面是我的代码

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

我还尝试读取局部变量中的值，然后在 PostExecute() 中分配给全局变量，如下所示。没有运气

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

请问有什么帮助吗？

Answer 1

查看您的评论，看起来您已经解决了问题，但我发布此答案是为了提供有关如何在 SSIS 脚本中使用变量以及如何解决类似问题的信息，因此它可以帮助其他人用户

SSIS 变量

变量存储可在所有 SSIS 组件和容器中使用的值。

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

在脚本组件中使用变量

每个脚本都有一个可以在脚本页面上定义的 ReadOnlyVariables 和 ReadWriteVariables 列表。

只读变量

ReadOnlyVariables 可以从所有脚本 Sub 访问，并且正如其名称一样，它们是只读。

读写变量

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

使用变量的方法

有 3 种使用变量的方法:

1. 在脚本页面中将它们选择为 ReadOnlyVariables 或 ReadWriteVariables 后直接访问它们

2. 使用变量分配器(LockForRead 和 LockForWrite 方法)

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

3. 使用 SSIS 日志记录任务:读取变量并将其记录到执行日志、消息框或文件

有很多文章讨论这个方法，你可以引用它们来了解更多

• 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