January 20, 2012 at 9:49 am
I have a Script Component inside a ForEach loop, which gives me this error -
[Script Component [13]] Error: System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)
The script runs fine when it's not inside a ForEach loop and I supply the variable by manually setting it.
Below is part of the script. If I enable the MsgBox, it confimrs that a variable value is being correctly set.
The variable is comming from a ResultSet of an Execute SQL Task which is outside of the ForEach loop.
Public Overrides Sub PreExecute()
Dim OFI_No As String
OFI_No = Me.Variables.OFINumber
'MsgBox(OFI_No)
Dim cmd As New SqlClient.SqlCommand("Declare @SessionID int; Exec " & _
"servername.OFI.dbo.sp_GetOFITypeAvvikelse @WHERE = 'dbo.Avvikelse.ID = " & OFI_No & "'", Conn)
sqlReader = cmd.ExecuteReader
End Sub
What am I missing?
Tim
January 23, 2012 at 4:42 am
You have no null handling on your string concatenation - try adding some, and see which section is failing.
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
January 26, 2012 at 8:43 am
Sorry for the delay in getting back to you (I got sidetracked on to some other issues).
But your right, it was a problem with NULL's. I thought I had tested for all possible occurrences, but had missed some.
And fixings the Null’s fixed the problem.
Many thanks
Tim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply