I have an external Excel spreadsheet which I am importing
via DTS.
The spreadsheet is divided into sections and therefore the
format of each section is different.
I've overcome most of the issues this raises, but one
remains. Seemingly the DTS engine makes it's decisions of
a rows type based on a sample using the first 8-10 rows. I
have a column that starts as numeric and further down
becomes a date. When I pull in this column it insists on
being a number rather than a date. Any ideas on how I can
force DTS to recognise this as the date it is?
(This happens in a data transform task, and I don't want
to use the Excel object, because this runs on the server)

You can import into a staging table with the date column defined as a
varchar and then use DTS to transfrom the data from this table to the table
you want it to be in.

Rand
This posting is provided "as is" with no warranties and confers no rights.

Not quite sure what is meant by this.

Let me put it another way. The code in a row transform
looks like this (remember it looks this way because the
columns of the row being imported depends on it's context
in the spreadsheet)

DTSDestination("DestColumn10") = DTSSource("Source10")

At the beginning of the spreadsheet, column "source10" was
a numeric. Now (at a larger row number in the spreadsheet)
it's a date, but a number representing that date is being
passed through as the resolution of the DTSSource call. I
need a date, which is being passed in other columns that
start and end life as a date.

Thanks for the help thus far!

What datatype is the DestColumn10 column defined as?

Rand
This posting is provided "as is" with no warranties and confers no rights.

DateTime

DTS is not going to recognize it as a date because the first rows of the
worksheet have numeric data. This is a characteristic of the Excel driver.

What you can do is create another table and rather than define this column
as a DateTime column, define it as a varchar. Then use DTS to import into
this table. After that you can use DTS to import this table to your desired
table. However a problem here is if the other numeric data can not convert
to dates this import might fail as well.

Rand
This posting is provided "as is" with no warranties and confers no rights.