Excel Destination errors for fields > 255 chars

Hi:

Am trying to write sql2005 data to Excel. I have problems when data length exceeds 255 chars. I used a sample destination file with cells containing >255 chars where required, so that the Excel Destination external column was recognized as DT_Text.

My oledb source columns(external and output) are both varchar (1000).

Works fine , but fails when values>255 chars.

Error 0xC0202009 on ProcessInput.

I use SQL2005 enterprise SP2.

I tried changing the Oledb source output to Textstream, but that does not work at all.

How do I export values >255 chars to excel?

TIA

Kar

Hello,

could you try using Import/Export wizard and saving the package it produces? It will likely use DT_STR on the source, DT_TEXT on the destination and insert a data conversion (DT_STR->DT_TEXT).

HTH.|||

Hi:

Using Varchar(Max) in the SQL table works both ways(excel source and excel destination) without any need to convert. I used the Import wizard as suggested, and alllowed it to create a new SQL table. Thanks for the tip.

Kar

|||

Hmmm, I rejoiced too soon.

Using varchaar(max) in SQL Destination works in the normal case, when the column contains >255 chars in the reference rows (as set by the GuessTypeRows registry value).

However, if the column contains <255 rows, then I get error 0xC0208265 - Failed to retrieve long data for Column.

My problem is that I have multiple xls files from which I import data to SQL. Now most files would have a given column with data >255 chars, but maybe one of the files has that column with all values <255 chars.

I thought I would just convert everything to text, and then use a Text File Source. Problem is that "Excel Automation - Save as text" gives me a series of hashes for cells with >255 chars.

So now I am stuck, and the only inelegant solution is to add a dummy first row to each excel and set guessTypeRows to 1 and force excel to get at least one cell>255 chars for all files..

UGH.

How do I make sure the package works for all cases? Somebody please help me!

TIA
Kar

|||

Well, you can either go with your solution or try to build two packages if you know which Excel files will have <255 char columns.

Thanks.