So I ran into an interesting problem that I haven’t found before on an SSIS import for SQL Server 2005. I was trying to import a large (2+ mil rows) text file that was a Ragged Right format and for some reason after 1.8 mil rows it would fail with the following error message.
[DTS.Pipeline] Error: Column Data for Column "Client" overflowed the disk I/O buffer
[DTS.Pipeline] Error: The PrimeOutput method on component "Client Source" (1) returned error code 0xC0202092. 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.
I like any person trouble shooting something went out to google and searched for “overflowed the disk I/O buffer”. I found a few good links on it
But none of these worked for me. I attempted to change the [LF] to [CR] to fix my problem. I tried different nvarchar and Unicode strings to allow these fields to come in but nothing ended up working.
Finally I had to open the file and look at it. I couldn’t easily open a 2 GB text file on this server through notepad so I wanted to find something else that could open the file quickly and let me see what was in the file. I asked on Twitter what others had used and several came up with UltraEdit. I went ahead and downloaded the trial and opened up the file it opened very quickly and worked perfectly to quickly show that at the 1.8 millionth row that it had been failing on it didn’t actually end at all. There was no [CR] or [LF] and the rest of the file was just filled with empty space so this data was trying to import the empty rows into all one field causing the overflow.
I’m not trying to make this a commercial for UltraEdit but I would suggest some sort of editor that can help you out when you’ve run through all your other methods. If you import lots of files with SSIS and many different formats then you should consider some sort of editor that can save you some time.
I’m working on a follow up post on my “typical” things I do in SSIS to try and get files to import.