Adding a Date Column though DTS

[jcoleman]

Is there any way to use DTS to add a column to a table that contains the current date? What I have now is a fixed lenght text file that I move into SQL using a DTS. I would like to add an extra column containing the current date into the table with the records from the text file. There must be a easy way of doing this but, I'm new.

 

[JohnDTampaBay]

Are you creating the destination table on the fly? If so, include a DateInserted column in the CREATE TABLE statement with a DEFAULT value of GetDate(). When rows are inserted into the table, this column will be filled with the current date. If you can't change the table structure until after the import, execute the following SQL statement against the destination table. Good luck!

ALTER TABLE YourTable
ADD DateInserted smalldatetime NOT NULL
   DEFAULT GetDate() WITH VALUES

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[jcoleman]

Thanks, John

I'm only going to create the table on the first run, but where would I enter the dateinserted column though the wizard? And if it's after the import how do I execute the SQL statement?

Sorry for being such a newbie.

 

[JohnDTampaBay]

I'm only going to create the table on the first run

Are you designing a DTS package that imports the same text file to the same table every time you run it? If so, after the table is created on the first run, use the code above to add the column. You can run it in Query Analyzer or an Execute SQL task in DTS. Once the DateInserted column has been added with the DEFAULT constraint, every time a row is inserted into the table, the current date will be inserted into this column.

If you are importing different text files to different tables and are using the wizard every time, just keep the code above handy (maybe put it in a stored procedure) and run it against whatever table you import the text file into.

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[jcoleman]

Thanks, again John.

I did have it working but couldn't understand why I was getting an error even when it worked. I did not realize that all I had to do was add that column once.

Thanks alot the my problem is fixed.