> 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?
>