May 9, 2008 at 3:48 am
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 ?
May 21, 2008 at 3:11 pm
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 If
'
End Sub
End Class
Hope this helps!
Kay Batta
May 21, 2008 at 3:16 pm
Might it make more sense to not hardcode the RecType in there?
Ben Sullins
bensullins.com
Beer is my primary key...
May 21, 2008 at 4:40 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply