DTS error when attempting to import from MS Access into SQL Server

Question

Post reply

DTS error when attempting to import from MS Access into SQL Server

Chris-232075

SSCrazy

Points: 2130
More actions
October 5, 2005 at 1:24 pm

#76433

Hi,

I’m attempting to use DTS to import data from a Memo field in MS Access (Jet 4.0 OLE DB Provider) into a SQL Server nvarchar(4000) field. Unfortunately, I’m getting the following error message:

Error at Source for Row number 30. Errors encountered so far in this task: 1.
Data for source column 2 (‘Html’) is too large for the specified buffer size.

I also get this error message when attempting to import the same data from Excel.

Per the MS Knowledgebase article located at http://support.microsoft.com/?kbid=281517, I changed the registry property indicated to 0. This modification did not help.

Per other suggestions in this forum, I moved the offending row from row number 30 to row number 1. This change only resulted in the same error message, but with the row number indicated as “Row number 1”. (Incidentally, the data in this field is greater than 255 characters in every row, so the cause described in the Knowledgebase article doesn’t seem to be my problem).

You might also like to know that the data in the Memo fields of the Access table was exported into this table from a SQL Server table using nvarchar(4000) as the data type. As a result, I don't see how the data I'm trying to import can exceed 4000 characters.

Does anybody know what might trigger this error message other than the data being less than 255 characters in the first eight rows (as described in the KB article)? If not, any suggestions on diagnosing the problem?

I’ve hit a brick wall, so I’d appreciate any insight.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

Also try this:

http://www.sqldts.com/default.aspx?254

Have you tried adding a few dummy lines at the top that aremore than 255 characters and seeing what happens?

May steer you in a different direction.

------------------------------
The Users are always right - when I'm not wrong!

sara karasik SSCommitted Points: 1573 More actions · Answer 2

What happens when you ask for left(ProblematicField, 500)? Will it import and truncate it at 500 characters?

Chris-232075 SSCrazy Points: 2130 More actions · Answer 3

Jonathon, I had tried your suggestion, but it didn't resolve my problem. Sara, I have not tested your approach. Instead, as a workaround, I took the following steps:

In my DTS package, I alter the applicable table to add a new column with a data type of 'text',
import into this 'text' column,
copy all data from this 'text' column into the corresponding nvarchar(4000) column,
drop the 'text' column.

This process works fine, although I'm disappointed that my DTS package has to go through the extra trouble.

Thanks for your feedback!