January 24, 2009 at 3:57 pm
2 Columns with 10-20 records are going into the Script Component (Configured as Destination) in data flow. First variable "Category" is String and the second "Delta" is a Boolean (SQL Bit). Category is Unique.
In the script I want to assign values to variables based on the Delta Column value i.e. True or False. Each Category will have its own variable.
If the Category is "Auto" and Delta is "True" - assign variable bitAuto = Delta.Value
If the Category is "PHONE" and Delta is "True" - assign variable bitPhone = Delta.Value
Here the no. of keys are known in advance so the no. of variables can be declared in advance. So here's what I did:
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 bitContENT As Boolean
Dim bitContAuto As Boolean
Public Overrides Sub INEntertainment_ProcessInputRow(ByVal Row As INEntertainmentBuffer)
If Row.Category = "ENTERTAINMENT" Then
bitContENT = Row.Delta
End If
If Row.Category = "AUTO" Then
bitContAuto = Row.Delta
End If
End Sub
Public Overrides Sub PostExecute()
MsgBox(bitContAuto.ToString, MsgBoxStyle.Information, "AUTO")
MsgBox(bitContENT.ToString, MsgBoxStyle.Information, "ENT")
End Sub
End Class
But lets say the no. of keys or variables are not known in advance and we want to assign values for each row. The processInput row is getting all rows in a buffer, but the postexecute task only gets the last row, so the following code in PostExecute doesnt work:
'If strCategory = "ENTERTAINMENT" Then
' Me.Variables.ContinueEntETL = bitContinue
'End If
'If strCategory = "AUTO" Then
' Me.Variables.ContinueAutoETL = bitContinue
'End If
How can I traverse through the records one by one in PostExecute to assign values to a variable.
Thanks.
September 8, 2011 at 10:13 pm
do you get the answer becouse i have the same proplem
September 9, 2011 at 11:50 am
Hi SolidSnake10_7
No one replied as you can see and to b honest I cant even remember what I was doing in Jan 2009. But It seems that I was trying to assign a value to a column in each row.
If script doesn't allow that then please use Derived Column Transformation Instead.
Or explain to me exactly what you are trying and I might be able to help.
Thanks
G
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply