Monday, July 26, 2010

DataFlow Script Component Variables

In SSIS the dataflow script component is pretty strict about how to pass on the variables to the component -- ReadOnlyVariables or ReadWriteVariables. The ReadWriteVariables variables can be used only if you are overriding PostExecute event
Public Overrides Sub PostExecute()
Variables.ErrorMessage = errorMessage
Variables.ErrorDescription = innerException
MyBase.PostExecute()
End Sub


I found the hard way that even if you are not writing to a variable but have passed the variable as a ReadWriteVariable to the component and accessing it in CreateNewOutputRows() method; it will throw an error.

And the error message is not very clear about what could be wrong. The error I kept getting was:
[Create output tables [1]] Error: System.NullReferenceException:
Object reference not set to an instance of an object.

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(IDTSManagedComponentWrapper90 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer90[] buffers, IntPtr ppBufferWirePacket)

No comments: