无法获取“ ReadWrite”消息。 SSIS脚本组件中的变量值 [英] Unable to fetch "ReadWrite" Variable Value in Script Component of SSIS
问题描述
在脚本组件[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变量
变量存储可在所有SSIS组件和容器中使用的值。
Variables Stores values that can be used in all SSIS components and containers.
Integration Services支持两种类型的变量:用户定义的变量和系统变量。用户定义的变量由程序包开发人员定义,而系统变量由Integration Services定义。您可以根据程序包的需要创建任意数量的用户定义变量,但不能创建其他系统变量。 此MSDN文章中的更多信息
在脚本组件中使用变量
每个脚本有一个 ReadOnlyVariables
和 ReadWriteVariables
可以在脚本页面上定义的列表。
Every script has a ReadOnlyVariables
and ReadWriteVariables
Lists that can be defined on the Script page.
ReadOnlyVariables
ReadOnlyVariables
可以从所有脚本Sub的访问并且它们被命名为只读
。
ReadOnlyVariables
can be accessed from all script Sub's and they are Read-Only
as they are named.
ReadWriteVariables
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:
- 在将它们选择为
ReadOnlyVariables
后直接访问它们或脚本页面中的ReadWriteVariables
-
使用变量分配器(
LockForRead
和LockForWrite
方法)
- Accessing them directly after having selected them as
ReadOnlyVariables
orReadWriteVariables
in the script page Using a Variables Dispenser (
LockForRead
andLockForWrite
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 Integration Services)中的脚本组件中读取和写入变量
- 在脚本任务中使用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
这篇关于无法获取“ ReadWrite”消息。 SSIS脚本组件中的变量值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!