无法获取“ReadWrite"SSIS脚本组件中的变量值

2021-12-30 00:00:00 etl vb.net sql-server ssis ssis-2012

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

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

错误:

为读写访问锁定的变量集合在 PostExecute 之外不可用.

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() 中分配给全局变量，如下所示.没有运气

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

有什么帮助吗?

推荐答案

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

SSIS 变量

Variables 存储可在所有 SSIS 组件和容器中使用的值.

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

Integration Services 支持两种类型的变量:用户定义变量和系统变量.用户定义的变量由包开发人员定义，系统变量由集成服务定义.您可以根据包的需要创建任意数量的用户定义变量，但不能创建其他系统变量.这篇 MSDN 文章中的更多信息

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 列表，可以在脚本页面上定义.

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

只读变量

ReadOnlyVariables 可以从所有脚本 Sub 访问，并且它们是 Read-Only，因为它们被命名.

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

读写变量

ReadWriteVariables 的集合仅在 PostExecute 方法中可用，以最大化性能并最小化锁定冲突的风险.因此，您不能在处理每一行数据时直接增加包变量的值.而是增加局部变量的值，并在处理完所有数据后在 PostExecute 方法中将包变量的值设置为局部变量的值.您还可以使用 VariableDispenser 属性来解决此限制.但是，在处理每一行时直接写入包变量将对性能产生负面影响并增加锁定冲突的风险.这篇 MSDN 文章中的更多内容

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 种处理变量的方法:

There are 3 Methods to work with variables:

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

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

  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();

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

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

    关于这种方法的文章很多，可以参考了解更多

    • VariableDispenser.GetVariables 方法(变量)
    • VariableDispenser.LockForWrite 方法(字符串)
    • 3方法-SSIS读写变量——脚本任务C#/VB.net
    • 使用 C# 在 SSIS(SQL Server 集成服务)的脚本组件中读取和写入变量
    • 在脚本任务中使用 SSIS 变量和参数

    • 相关文章