SSIS Importing Files

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

http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/e312612c-acbf-45e3-b773-3fd84466b4fb/

http://www.bigresource.com/MS_SQL-Column-overflowed-the-disk-I-O-buffer-V666XXcc.html

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.

Pat

Advertisements

6 responses to “SSIS Importing Files

  1. Pat, thanks for this post. After encountering the same problem and having tried all of the things you mentioned I came across this post and, after downloading the editor and loading up my 2GB file, I find the exact situation you found, zeros and a corrupt file.

  2. Great Michael glad it helped you to find the solution. pat

  3. Greate Pat… Thxx man. I had same problem and did the same as you but could not solve the issue. After reading this, just checked the data file and was astinished that it had some erronious data at the end, that was causing problem. Thanks again Pat.

  4. Hi there, had the same issue, but mine turned out to be a single stray double quote in the text file. That opened the quote in the interpreters and the rest of the file was interpreted as being a text value! Kept looking in Notepad++ and Programmer's Notepad and couldn't see the 0's or anything nasty like you all found.. so anyway, here's yet another potential reason for this error

  5. the problem of data corruption in the files of specified format can be fixed by the sql server repair mdf utility

  6. Pat, thanks for this – I found my data ended at 4M when the file had 12M rows! I wouldn’t have found this without your recommendation for UltraEdit. Nice!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s