时间戳

首页 > 解决方案 > 在数据流任务中增加变量

sql-server - 在数据流任务中增加变量

问题描述

我有一个大文本文件，其中有多个不同的行。

我正在使用条件拆分，它查看行类型（字段 1）然后采取行动，主要是尝试增加一个变量，将单行拆分为多列（派生），然后将结果写入表。

但是，当尝试增加变量时，我得到“锁定以进行读写访问的变量集合在 PostExecute 之外不可用。”

该变量正在使用脚本组件进行更新。

我曾尝试将代码移动到 PostExecute，但是在那时，它似乎永远不会增加。


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    Dim MaximumKey As Int32 = Me.Variables.SPIParentID ' Grab value of MaxKey which was passed in

    ' NextKey will always be zero when we start the package.
    ' This will set up the counter accordingly
    If (NextKey = 0) Then
        ' Use MaximumKey +1 here because we already have data
        ' and we need to start with the next available key
        NextKey = MaximumKey + 1
    Else
        ' Use NextKey +1 here because we are now relying on
        ' our counter within this script task.
        NextKey = NextKey + 1
    End If

    'Row.pkAAAParentID = NextKey ' Assign NextKey to our ClientKey field on our data row
    Me.Variables.SPIParentID = NextKey
End Sub

我希望能够使用我已经到位的条件拆分来循环文件，然后当它达到某个记录类型时，它将采用当前的 RecordTypeID 并将其递增，然后写出到下一条记录。

标签： sql-servervb.netssisetlscript-component

解决方案

SSIS 变量值不能在数据流任务中更改，该值在整个数据流任务执行后更改。每当您尝试从此变量中读取值时，它将在执行数据流任务时返回其值。您可以更改使用脚本中的局部变量来实现相同的逻辑：

Dim MaximumKey As Int32 = 0 

Public Overrides Sub PreExecute()

    MyBase.PreExecute()

    MaximumKey = Me.Variables.SPIParentID ' Read the initial value of the variable

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

    ' NextKey will always be zero when we start the package.
    ' This will set up the counter accordingly
    If (NextKey = 0) Then
        ' Use MaximumKey +1 here because we already have data
        ' and we need to start with the next available key
        NextKey = MaximumKey + 1
    Else
        ' Use NextKey +1 here because we are now relying on
        ' our counter within this script task.
        NextKey = NextKey + 1
    End If

    'Row.pkAAAParentID = NextKey ' Assign NextKey to our ClientKey field on our data row
    Me.Variables.SPIParentID = NextKey
End Sub

Public Overrides Sub PostExecute()

    MyBase.PostExecute()

    Me.Variables.SPIParentID = MaximumKey ' Save the latest value into the variable

End Sub

推荐阅读