I am migrating data from mainframe to SQL Server 2000.

The mainframe has a utility that dumps all the datatables
in the mainframe database as .csv files to a windows
system. There is another utility that gives me the schema
for each datatable.

I upload these .csv files to SQL server using the DTS
wizard, where I transform the text data to the required
datatype using a sql Create Table statement that I
generate out of the schema, for that table.

All the datatypes other than DATETIME work perfectly.

When I specify the datatype as datetime for a particular
column, DTS fires an error saying that it could not
convert the said value to "datetime" from "string".

My "datetime" values in the .csv are like "15DEC00:22:30".
Is there any way to convert this into datetime within the
DTS wizard itself? If so, how?

tia,
Rajesh

No hope that I know of in the wizard but this will help
you do it in the designer:

declare @var1 varchar(20),@day varchar(2),@month varchar
(3),@year varchar(2),@time varchar(5),@chardate varchar
(50),@realdate datetime
set @var1 = '15DEC00:22:30'
set @day = left(@var1,2)
set @month = substring(@var1,3,7)
set @year = substring(@var1,6,8)
set @time = right(@var1,5)
set @chardate = case when @year < 00 then '19' + @year
else '20' + @year end + '-' + @month + '-' + @day + ' ' +
@time + ':00'
set @realdate = cast(@chardate as datetime)
select @chardate
select @realdate

HTH

Ray Higdon MCSE, MCDBA, CCNA