Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob