Post reply

DTS failed

  • 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.

  • September 19, 2002 at 9:28 pm

    #436940

    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 .

  • September 20, 2002 at 5:20 am

    #436963

    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)

  • September 20, 2002 at 8:22 am

    #436978

    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!

  • September 20, 2002 at 10:20 am

    #436988

    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

  • September 20, 2002 at 10:31 am

    #436989

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

    You guy's are great.

    Thanks a lot.

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

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