dlangschied (2/27/2013)

---

I am using the DTS Wizard. I am pulling in the worksheets from Excel and the Wizard is assigning fields by column and then is some how setting the field type and size.

I am a newbie. This wizard does look like it is simple enough to use. I have used it a few times to copy tables to Excel for loading in other SQL databases. I have had an issue with text fields, but it has not hurt me to simply ignore those fields until now. On the varchar field, that is the first time i have experienced it.

I am providing a snap shot of the varchar field. You can see here that the varchar is 255, yet to the right it determines the field to be 800. The problem may very well be that the Excel spreadsheet is telling SQL that is its size. I just do not know enoght to know.

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.