Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

no matter how large the destination column eg. nvarchar(max), excel source choke on column Expand / Collapse
Author
Message
Posted Friday, June 7, 2013 9:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:23 PM
Points: 315, Visits: 91
I am dealing with a similar issue, and found this exerpt in the article referenced below. Check and see if it applies to you:

•Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error.


http://technet.microsoft.com/en-us/library/ms141683(v=sql.90).aspx



Post #1461128
Posted Friday, June 7, 2013 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
Tab Alleman (6/7/2013)
I am dealing with a similar issue, and found this exerpt in the article referenced below. Check and see if it applies to you:

•Truncated text. When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key. For more information, see PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error.


http://technet.microsoft.com/en-us/library/ms141683(v=sql.90).aspx


That's pretty much what the blog post we've been discussing says, except in much more practical terms than the TechNet documentation.

opc.three (5/16/2013)
The whole post is a good read, but section "The solution part III" pertains to your issue specifically:

http://blogs.lessthandot.com/index.php/DataMgmt/ssis-1/what-s-the-deal-with



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1461134
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse