John,

I'm coming late to this thread. Have you resolved the original issue? Here's
one summary of the issue.

"When the driver determines that a column contains string data, it treats
the column as a 255-character column instead of a memo column if it does not
discover any values longer than 255 characters in the rows that it samples.
(When analyzing text and memo columns, the driver guesses in favor of the
longest value that it discovers, not the majority type.) Therefore values
longer than 255 characters may be truncated. You must make sure that the
memo columns in at least one of the first eight rows contain values longer
than 255 characters, or you must increase the number of rows sampled by the
driver. For information about the latter solution, see PRB: Transfer of Data
from Jet 4.0 OLEDB Source Fails w/ Error."

Let me know if you've tried both these approaches without success. These are
age-old problems with the Excel driver and not DTS-specific.

-Doug


--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

hi doug,

yes i have resolved the original issue by installing office 2003 and
adding a new value in the extended properties (see steve's response and
my closing comment).

you mentioned this is an "age-old" problem with the excel driver, so i
guess newer office version has taken care of it and that's fine. but i
was wondering (just for curiosity-sake) if you can suggest another
approach outside of using the excel driver?

thank you for your attention,
~ js.


Douglas Laudenschlager [MS] wrote:

John,

What solved your issue was either the import mode setting, or the greater
number of rows being sampled...a more recent version of Office would not
influence this Jet database engine behavior.

To the best of my knowledge, the Jet database engine and its supporting ISAM
driver is the only prepackaged Microsoft technology for reading binary Excel
files as data sources. (I say "prepackaged" because the Excel file format is
documented and a developer could write a different driver. I say "binary"
because you could output the Excel data as CSV or XML.) Whether you're using
the Jet Provider, or ODBC, or Office's data tools, or SQL Server DTS, or
even SQL Server 2005 Integration Services with Excel files, you're using Jet
behind the scenes and inheriting its...eccentricities.

While Automation would be another approach for getting data out of Excel,
running Office apps programmatically and unattended on the server is
strongly discouraged.

I hope this answers your question.

-Doug


--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.