Vague SSIS error message — SSIS Error Code DTS_E_PROCESSINPUTFAILED
So today I got the error message below
DFT Extract interface_RiskScorecard: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.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket) DFT Extract interface_RiskScorecard:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Parse Scorecard Name into properties" (66) failed with error code 0x80004003 while processing input "Input 0" (76). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
So this happened when I passed some names through RegEx in a script task to split it into different parts. If you look at the error there are some things that stand out:
- NullReferenceException: Object reference not set to an instance of an object.
- Parse Scorecard Name into properties
- Input 0
Ok, so this sort of points me in the direction of where the error happened, well at least which task caused it. In my script task I’m setting the values of 4 output columns with the results of different RegEx expressions. Now you will notice this error does not point to the line in the code or which function it was. The only thing I had to go by was the “object reference…” message. This led me to looking for working with an object that could potentially be null when I’m trying to manipulate it. Eventually I found it, I was referencing the Length property on a string with a null value in an If Statement, but I did not expect it to be a null value. To fix it, I just had to add “myStrProp != null” to test nullability first before accessing the Length property. This is a very common error in C# programming and easy to fix. I just wish the error messages from SSIS script components gave a better message as to where the error is.