February 27, 2009 at 6:08 am
Does anyone know how i can read variables into a script, assign them to variables within the script, and after a bit of work assign these back to the same variables (so that i can read them back in when the next row is processed) - in the Script Component Task.
I have tried moving the code where i write back out to the PostExecute sub but everything i've tried keeps coming back with the "The collection of variables locked for read and write access is not available outside of PostExecute" error message.
I am attempting to read in a table of IP Addresses and node counts and compress where sequential so if i have the following:
IP Address Nodes
1 10
10 10
then i want to get out
IP Address Nodes
1 20
The code i have is below. If anyone can tell me where i'm going wrong it would be much appreciated.
Imports System
Imports Microsoft.SqlServer.Dts.Pipeline
Public Class ScriptMain
Inherits UserComponent
Private normalBuffer As PipelineBuffer
Dim iIPAddress As ULong
Dim iNodes As Integer
Dim iIPAddressStart As ULong
Dim iIPAddressCurrent As ULong
Public Overrides Sub Input0_ProcessInputRow _
(ByVal Row As Input0Buffer)
iIPAddressCurrent = Variables.IPAddressCurrent
iIPAddressStart = Variables.IPAddressStart
iNodes = Variables.Nodes
iIPAddress = Convert.ToUInt64(Row.IPAddress)
If iIPAddress = iIPAddressCurrent Then
iNodes += Row.Nodes
Else
With normalBuffer
.AddRow()
iIPAddressStart = Variables.IPAddressStart
iNodes = Variables.Nodes
.SetUInt64(0, iIPAddressStart)
.SetInt32(1, iNodes)
End With
iNodes = Row.Nodes
End If
End Sub
Public Overrides Sub PrimeOutput(ByVal Outputs As Integer, _
ByVal OutputIDs() As Integer, _
ByVal Buffers() As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
' save underlying PipelineBuffer to allow for calls to
' PipelineBuffer methods
normalBuffer = Buffers(0)
MyBase.PrimeOutput(Outputs, OutputIDs, Buffers)
End Sub
Public Overrides Sub PostExecute()
Variables.IPAddressCurrent += Convert.ToUInt64(iNodes)
Variables.IPAddressStart = Convert.ToUInt64(iIPAddress)
Variables.Nodes = iNodes
MyBase.PostExecute()
End Sub
End Class
thanks,
Matt
February 27, 2009 at 7:39 am
I think I experienced a similar issue and I learned that the Script Component is instantiated (I think that would be the correct term) once during the Data Flow so Class Level variables are created once and live for the lifetime of the Script Component. Also I believe that PostExecute only runs when the entire dataset is processed.
So if you are trying to do what I think, save the previous row's data to compare to the next row's data then you do not need the Package variables and I think this would work for you:
Imports System
Imports Microsoft.SqlServer.Dts.Pipeline
Public Class ScriptMain
Inherits UserComponent
Private normalBuffer As PipelineBuffer
Dim iIPAddress As ULong = 0
Dim iNodes As INTEGER = 0 ' set the default here
Dim iIPAddressStart As ULong = 0 ' set the default here
Dim iIPAddressCurrent As ULong = 0 ' SET the DEFAULT here
Public Overrides Sub Input0_ProcessInputRow _
(ByVal Row As Input0Buffer)
iIPAddress = Convert.ToUInt64(Row.IPAddress)
If iIPAddress = iIPAddressCurrent Then
iNodes += Row.Nodes
Else
With normalBuffer
.AddRow()
iIPAddressStart = Variables.IPAddressStart
iNodes = Variables.Nodes
.SetUInt64(0, iIPAddressStart)
.SetInt32(1, iNodes)
End With
iNodes = Row.Nodes
End If
iIPAddressCurrent += Convert.ToUInt64(iNodes)
iIPAddressStart = Convert.ToUInt64(iIPAddress)
End Sub
Public Overrides Sub PrimeOutput(ByVal Outputs As Integer, _
ByVal OutputIDs() As Integer, _
ByVal Buffers() As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)
' save underlying PipelineBuffer to allow for calls to
' PipelineBuffer methods
normalBuffer = Buffers(0)
MyBase.PrimeOutput(Outputs, OutputIDs, Buffers)
End Sub
End Class
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2009 at 7:54 am
You are exactly right - i want to compare a field in the current row to a calculated value from the previous row.
I had actually tried something similar in a previous iteration of my code.
With your code i still get the "The collection of variables locked for read and write access is not available outside of PostExecute" error, but thanks for trying. I've been looking at previous posts on which you commented so if you have any more thoughts on my predicament i'd be most grateful.
Also is it just me, but does anyone else get so frustrated that ReadWrite variables aren't available to be read from and written to. I'm thinking of sueing Microsoft on the trade descriptions act as it doesn't do what is written on the tin!!! I'm getting so frustrated.
What about using the variableDispenser object to access the variables?
February 27, 2009 at 8:11 am
I think you actually missed my point, which is understandable since I probably did not make it clearly, so here it is in what I hope is a clear manner.
You do not need to use package variables in a script component unless you want to be able to access the variables OUTSIDE the script component. REMOVE all references to the variables in the script component.
Attached is a working package that queries the AdventureWorks.Person.Contact table returning a sorted list of last names. I then use a Script Component to compare the current rows last name to the last rows last name and increment a class level LastNameCount variable which I then put into an output column I created in the Script Component (LastNameCount). I then output the data to a text file. You just need to rename the file, removing the .txt and replacing the "_" with a "."
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2009 at 8:16 am
Matty,
Try and declare the variables at the package level instead of inside the script, and then reference tham inside the script from the DTSVariables collection. I THINK that you are having scoping issues because they are declared within the script instead of outside.
If I am not mistaken, the class will be instantiated for each row, not once for the task.
HTH
Rich
February 27, 2009 at 8:18 am
Never Mind. I think Jack has it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply