Using a global variable inside a script component

Question

Using a global variable inside a script component

pvgass

Old Hand

Points: 366
More actions
May 9, 2008 at 3:48 am

#187450

I am changing the layout of a flat file by using a global variable and a script component in the data flow.
The file has different record types and I use the variable to store a particular value found on one record type so as to write it out again on a different record type. I am making use of the variable dispenser property and here is the code:
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim SavedBatchNo As String
If Row.RecCode = "DEPH" Then
VariableDispenser.LockForWrite(SavedBatchNo)
VariableDispenser.GetVariables(SavedBatchNo)
SavedBatchNo = Row.DepBatchNo
Else
If Row.RecCode = "DEPD" Then
VariableDispenser.LockForRead(SavedBatchNo)
VariableDispenser.GetVariables(SavedBatchNo)
Row.StBtchNo = Variables.SavedBatchNo
End If
End If
'
End Sub
End Class
The problem is that the GetVariables action is not recognising 'SavedBatchNo' because of the error:
Option Strict On disallows implicit conversions from 'String' to 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables90'
Can someone explain what I am doing wrong ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kbatta SSCrazy Points: 2274 More actions · Answer 1

Define fields whose values you want to keep from one input row to the next after the "Inherits UserComponent" and before the "Public Overrides...".

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Dim SavedBatchNo As String

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.RecCode = "DEPH" Then

SavedBatchNo = Row.DepBatchNo

Else

If Row.RecCode = "DEPD" Then

Row.StBtchNo = SavedBatchNo

End If

'

End Sub

End Class

Hope this helps!

Kay Batta

Ben Sullins-437405 SSCertifiable Points: 5397 More actions · Answer 2

Might it make more sense to not hardcode the RecType in there?

Cheers,

Ben Sullins
bensullins.com
Beer is my primary key...

kbatta SSCrazy Points: 2274 More actions · Answer 3

If you are referring to the 2 RecCode values, pvgrass could put the desired values into variables and bring them in. Then the same code could be used for other combinations of "read from" RecCode and "write to" RecCode. But my guess is DEPH represents a header row and DEPD represents a detail row.

What really gets interesting is if you also have a trailer row - and multiple sets of header, zero or more details, trailer. There can be multiple sets of these in one file. And you need to associate each header and trailer with their detail rows. That is a multiple step process.