DTS failed

Question

DTS failed

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
September 19, 2002 at 7:53 pm

#80129

Can somebody explain me why I am getting following error while downloading excel file into database.
Error at source for Row Number 145. Error encountered so far in this tast:1. Data for source column 10 ('F10') is too large for the specified bufffer size.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

Can you tell me what type of column is F10 in Excel? It seems the problem is in the data source (In excel), not in the DTS package.

Or you can try changing the table in which you are importing .

Antares686 SSC Guru Points: 125444 More actions · Answer 2

When using DTS to import or with a Excel file the DTS wizard only scans the first x number of records (sorry I forget that number but is somewhere on MS site) and makes a size determination from that based on type of field not actuall length if I remember right. If you are allowing it to decide for you it may be wrong. In the transformation of the DTS package from Excel to your table on the destination tab make sure the defined columns are large enough for your longest bit of data in the column.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

John Boals Old Hand Points: 321 More actions · Answer 3

Take a look at this kb article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q281517

I had this problem a while back. Antares is right; by default, DTS searches 8 rows and makes a determination of the column's data type. In my case (and most likely in yours), the column had text data. I had dts automatically create the sql server table, in which it created a varchar(255) column.

Unfortunately, somewhere in the spreadsheet someone had put about 4K of text into a cell. It choked with the data too large error.

The weird thing was, even if I changed the data type in the destination table from varchar(255) to text, I still got the error. The only resolution was to do what the kb article says, which is to change the registry and make dts look at the whole file before making the determination.

Or, you could do what I really recommend, and don't use Excel as your source in the first place. What a pita!

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 4

Thanks for your reply,

It's not a length problem. I changed F10 filed to varchar(8000) and still getting an error.

I cannot get rid of Excel file as a source because my DTS package automatically searches an excel file which are comming from other country.

The point is how to fix this problem.

Thanks in advance

balbirsinghsodhi SSCrazy Eights Points: 9210 More actions · Answer 5

Wow, I changed the registry setting and it's working now.

You guy's are great.

Thanks a lot.