sql server dts: Guys,

I am trying to create a package where an Excel file is imported to a SQL
Server 2000 Database. Some columns contain text that is larger than 255
Characters. When I try to import I get the following error message:
Data for source column 6 ('....') is too large for the specified buffer size.

Datatype for the specific column is nvarchar (1000). Any ideas how to
overcome this issue?


Thanks a lot!

[quoted text, click to view]

Hi Marios,

I suspect that this problem is actually being thrown by your ETL
process rather than by the database itself ... particularly if you're
using SSIS (SQL 2005) to load your data.

The most likely culprit is the way that Excel evaluates column
widths. Basically, what it does by default is look at the first 8
columns, and use those to guess the length and type of all subsequent
rows. In SSIS you will see this in your Excel source connection if
you look at the widths of the columns it has created. Unfortunately,
it is not possible to change these column widths. Well .. actually,
you can change the column widths, but you can't change the widths of
the associated error columns, and then the package will error saying
that the error columns don't match the input columns, and convert the
input ones back to what they were automatically detected to be.

So .. to get around this, you have 2 options:
1) Change some obscure (but reasonably well documented) registry
setting *and* your Excel file connection string to force the
connection to parse the entire file before guessing the column
metadata. You may run into trouble with this method if you are
working on a client's or managed server ... it all comes down to
server admin policy.
2) Stick a dummy row at the top of your Excel spreadsheet which forces
it to evaluate the columns correctly. In other words stick in a row
with data that matches your maximum width for each column.

I stumbled across this blog (http://blog.lab49.com/?p=196) about a
year ago that summarises the issue quite nicely (although in the
context of ADO.NET)

Good Luck!
J

[quoted text, click to view]

What I actually meant to say is that it looks at the first 8 *rows* to
guess the *column* lengths of all subsequent rows.

Sorry for any confusion!
J