I am trying to append records from a .csv file into an existing table in
SQL Server 2000 using DTS.

The existing table looks like this:

Field1 Field2 Field3
A 10% 1/3/2005
B 11% 1/4/2005
C 8% 1/4/2005

The .csv file only has two fields that I want to import into Field1 and
Field2 respectively. I would like Field3 (a datetime field) to
automatically populate with yesterday's date at the time of the data
import.

I have been able to accomplish this when importing .csv files into a new
table with the DTS wizard by using SQL statements like the following:
"datetime NOT NULL default (convert(char(8), getdate( )-1, 112)))" as
part of the command to create a new date field.

However, there does not seem to be a way to modify the SQL statement in
this fashion within the DTS Wizard when appending records from a .csv
file to an existing table.

Does anyone have any suggestions for how to make a datetime field
automatically populate with yesterday's date when adding rows to an
existing table with DTS?

Thanks.





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

In a standard DTS package, one way of doing this would be to use an
ActiveX transform instead of the usual Copy Column type of transform
that is usually used. Within the DTS Import wizard you can do this from
the "Select Source Tables and Views" window by clicking on the "..."
button under Transforms. Go to the "Transformations" tab and select
"Transform information as it is copied to the destination." From there
you can fill in code to set the destination column.

HTH,
-Tom.

A Text file does not accept any kind of sexy syntax so what you could do
is place a default on the Field3 and do not try and populate it.

You could also use an ActiveX Script transform to do this for you.

"Demian" <demian (AT) devdex (DOT) com> wrote