I'm trying to move some data from a tab delimited text file into a SQL
Server 2000 database table using DTS and have run into a problem
converting text data into smalldatetime. The format for the dates in
the text file is CCYYMMDD, all text fields are double quoted.

I created the initial transform data task using the wizard and then
opened it up in the designer to make changes. I select the Transform
Data Task, then click on the Transformation tab and set Phases Filter
to Row Transform, Name to None Selected, Type to None Selected. Then I
click on the Delete All button to remove existing transformations, and
New to create the new transformation. I select DateTimeString, set the
properties for the source and destination formats, then select the
Source Columns and the Destination Columns and click OK. At this point
I get the following error message:

"Error Description: TransformDateTime String: Must have one source
column and one destination column, and each must be compatible with
datetime format."

I found that if I only select one date field from the source file and
one from the corresponding destination field in the database table, the
DTS job is happy and the transformation is created. It's when I try to
add the conversions for the other date fields and the remaining text
fields that the error message is generated.

Anyone have any idea what I'm doing wrong?

Hello trianbmt (AT) dfw (DOT) wa.gov,

Whenever I want to mave dates from a text file to SQL Server and they are
in this format I tend to always use this example

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com