I have a DTS package which exports from a SQL table to an Excel
spreadsheet - nothing tricky so far. The (SQL Server) source table
contains several columns which have more than 255 chars of data. When I
created the destination worksheet in DTS Designer, I specified LongText
as the data type for the long columns and all was well. I added some
conditional formatting to the destination, and then I added a little
VBS code which copies the empty template destination worksheet into the
real destination workbook, updates the package source & destination on
the fly and runs the export task, and now all my data gets truncated at
255 columns.

Further investigation shows that if I look at the column definitions in
the template worksheet using the Destination tab of the "Transform Data
Task Properties" dialog box, it has "remembered" the LongText column
data types, but in the destination workbook (i.e. the workbook where
I've copied the template worksheet), the columns are all VarChar( 255
).

So what I really want to know is: where is Jet/Excel storing these
column definitions/metadata? It's not in the
DestinationColumnDefinitions collection, because that's empty, both in
Disconnected Edit and at runtime in the debugger...

This one is really getting to me as I'm looking at reverting to a pure
automation (not DTS) solution if I can't find a way around this, and so
far I've always been able to make DTS do what I want.

Thanks in advance...

Alasdair Cunningham-Smith
Running SQL 2000 SP3

Update:

http://support.microsoft.com/?kbid=303814 has this to say:

"When you use ADO to insert or modify data in the tables and columns
that you have created in Excel using ADOX, ADO respects the data types
that you specified for those columns, although *it is not clear where
this information is stored*."

(My stars for emphasis)

Looks like magic to me as well. You can use ADO schema rowsets to
examine the structure of the columns in a worksheet. If you've created
the worksheet with ADOX or equivalent CREATE TABLE commands, then the
adSchemaColumns rowset shows the proper datatypes. After you copy and
rename the worksheet, the same schema rowset returns all varchar(255)
columns - the metadata (data types) are lost. I thought the metadata
may be related to the range, but the metadata is present in the default
range for the sheet (ending in a $ sign), but lost after the
copy/rename. Weird.

My workaround will have to be to create the worksheet in the
destination using CREATE TABLE and then reapply the conditionaly and
other formatting through COM automation calls. Nasty, but not DTS's
fault.

Alasdair.