Object reference not set to an instance of an object. - SQL Server 2005 Script
-
I am writing VB .NET code as a part of script when accessing the script editor in the scrit compoenet in SQL Server 2005. I am trying to read data from a flat file that is not in one of the standard formats like csv or tab delimited and when running the app I get the following error:
Object reference not set to an instance of an object.
at ScriptComponent_18cb4a15976a4641a03b8d6eb3dca3cf.ScriptMain.CreateNewOutputRows()
at ScriptComponent_18cb4a15976a4641a03b8d6eb3dca3cf.UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
Here is the code I am using, any input in getting this resolved will be much appreciated:
' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim path As String = "C:\\Temp\\Apple 2006-01-08-01-14 Job ZXW_00411011P Step 1.TXT"
Dim oStreamReader As StreamReader
Public Overrides Sub CreateNewOutputRows()
Dim counter As Integer
Dim sLine As String
Dim a() As String
Dim splitChar() As String
Dim cntr As Integer
splitChar(0) = "|"
splitChar(1) = " "
Try
oStreamReader = New StreamReader(path)
For counter = 1 To 5
sLine = oStreamReader.ReadLine().ToString
Next
a = sLine.ToString.Split(splitChar, StringSplitOptions.None)
Do While a(0) IsNot Nothing
Me.AppleOutputBuffer.AddRow()
Me.AppleOutputBuffer.SKUOutput = a(1)
For counter = 3 To (a.Length - 1)
If (a(counter) <> splitChar(1)) Then
Me.AppleOutputBuffer.SLoc = a(counter)
cntr = counter
Exit For
End If
Next
For counter = cntr To (a.Length - 1)
If (a(counter) <> splitChar(1)) Then
Me.AppleOutputBuffer.TransactionType = a(counter)
cntr = counter
Exit For
End If
Next
For counter = cntr To (a.Length - 1)
If (a(counter) <> splitChar(1)) Then
Me.AppleOutputBuffer.S = a(counter)
cntr = counter
Exit For
End If
Next
For counter = cntr To (a.Length - 1)
If (a(counter) <> splitChar(1)) Then
Me.AppleOutputBuffer.TransactionType = a(counter)
cntr = counter
Exit For
End If
Next
For counter = cntr To (a.Length - 1)
If (a(counter) <> splitChar(1)) Then
Me.AppleOutputBuffer.Tracking = a(counter)
cntr = counter
Exit For
End If
Next
sLine = oStreamReader.ReadLine().ToString
a = sLine.ToString.Split(splitChar, StringSplitOptions.None)
Loop
Me.AppleOutputBuffer.SetEndOfRowset()
Catch ex As Exception
Me.ComponentMetaData.FireError(1, "Script Component", ex.Message, "", 0, True)
Finally
oStreamReader.Close()
End Try
'
' Add rows by calling AddRow method on member variable called "<Output Name>Buffer"
' E.g., MyOutputBuffer.AddRow() if your output was named "My Output"
'
End Sub
End Class
Thanks,
Manisha
-
Its virtually impossible to say what's wrong from the information you have given. You need to debug the code using the built-in features that enable you to do that. Put a breakpoint at teh start of the code block and step through the code until it fails.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
I'll take a stab at it and say th script is not aware of the "AppleOutputBuffer" object as I see no reference to it within the code.
Cliff
-
The buffers are declared outside of this and I dare say that Manisha used Intellisense on this object to call methods on it - which would prove that the use of it isn't that.
I think
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
The AppleOutputBuffer is automatically declared when create an output component in the Inputs and Outputs section of the Script Transformation Editor. The PrimeOutput sub procedure that instantiates and initializes the buffer does so when the OutputIDd(I) = 4 only I am not sure why that is the case? This code is auto generated by the Visual Studio 2005 app when I declare an output component and add columns to it. Here is the auto-generated code. Also I am not sure how to debug this code with break point as when I say Start Debugging, it runs the whole project and does not stop execution at the breakpoint on the script even if it is on the first line. Any inputs will be appreciated. The function possibly causing the problem is in bold below:
' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
' Microsoft SQL Server Integration Services component wrapper
' This module defines the base class for your component
' THIS IS AUTO-GENERATED CODE THAT WILL BE OVERWRITTEN! DO NOT EDIT!
Imports
System
Imports
System.Data
Imports
Microsoft.SqlServer.Dts.Pipeline
Imports
Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports
Microsoft.SqlServer.Dts.Runtime.Wrapper
Public
Class UserComponent
Inherits ScriptComponent
Public Connections As New Connections(Me)
Public Variables As New Variables(Me)
Public AppleOutputBuffer As AppleOutputBuffer
Public Overridable Sub FinishOutputs()
End Sub
Private Sub MarkOutputsFinished()
If AppleOutputBuffer IsNot Nothing Then
AppleOutputBuffer.SetEndOfRowset
AppleOutputBuffer = Nothing
End If
End Sub
Public Overrides Sub PrimeOutput(ByVal Outputs As Integer, ByVal OutputIDs() As Integer, ByVal Buffers() As PipelineBuffer)
For I As Integer = 0 To Outputs - 1
If OutputIDs(I) = 4 Then
AppleOutputBuffer = New AppleOutputBuffer(Buffers(I), GetColumnIndexes(OutputIDs(I)))
End If
Next
CreateNewOutputRows()
FinishOutputs()
MarkOutputsFinished()
End Sub
Public Overridable Sub CreateNewOutputRows()
End Sub
End Class
Public Class Connections
Dim ParentComponent As ScriptComponent
Public Sub New(ByVal Component As ScriptComponent)
ParentComponent = Component
End Sub
Public ReadOnly Property Connection() As IDTSConnectionManager90
Get
Return ParentComponent.ComponentMetaData.RuntimeConnectionCollection.GetObjectByID(61).ConnectionManager
End Get
End Property
End Class
Public Class Variables
Dim ParentComponent As ScriptComponent
Public Sub New(ByVal Component As ScriptComponent)
ParentComponent = Component
End Sub
End Class
Thanks,
Manisha
-
Ah yeah, sorry. Debuggin doesn't work in the script component. Your best bet is to raise some OnInformation events at various points at the code - this works a bit like a message box saying "Got to here".
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Use exception handling and use a messagebox to show the exception with the stack etc. It will show you exactly where the callsite was that caused the error or at least get you very close.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply