Not really a blog post of the typical form, this is more so add content to the Internet and hopefully have Google find it for someone else.

So, I inherited a DTSX package from a former project. Who hasn’t been in that position before, right?

No problem I could make most of it do what I wanted except for ONE Data Flow Task. Or more accurately, ADO NET Source.  This was connecting to a 3rd party database on the client server.  Not a problem, except I can’t hit that 3rd party database from my desktop and, unfortunately, I can’t install Visual Studio on the client’s server. So, for most of my changes, I had to disable that data flow task to make my other edits.  Annoying, but not a show-stopper in this particular case.

Until… I had to actually edit that Source.  I could not add new OUTPUT Columns under the Source Output.  I think this is because I couldn’t connect to the actual data source to validate stuff. I could be wrong. But anyway, I had to resort to editing the XML directly. This is always a bit dangerous, but Danger is my middle name. (Ok, maybe not, but my middle initial IS D.)

And then I committed my changes, loaded it to the client computer and ran it.

Well, sort of.  The data flowed like it should and then I got:

System.NullReferenceException: Object reference not set to an instance of an object.   at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.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 other error was:

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on ADO Source returned error code 0x80004003.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure

I added some more error handling and tried everything, but, I couldn’t stop the error, even though all the data actually was flowing.  This was weird. The data WAS flowing exactly the way I wanted. But, the package would fail due to the above error.

I finally created a test package with JUST the Data Flow Task and tried debugging that. I still had no luck. But at least the XML was far easier to parse.

After looking at it for the 42nd time, I finally noticed… I had added the column to the Output Columns under the ADO NET Source Output, but I had NOT put it under the ADO NET Source Error Output.

So, even though there were no errors, apparently DTSX would still fail because of the missing column. Once I added that, everything was solved.