I have a SSIS package that imports a CSV file to a database and this package will be run on a daily basis at the moment it appends new rows to the bottom of the table and there is no datetime column in the destination table.
What I want to do is create a new date/timestamp in the destination table and every time the data loads I want the table to reflect the load time from the database Timestamp.
I'm not sure how I go about achieving this.
Alter your destination table to include the new column, with the default value of your datetime function of choice.
ALTER TABLE DataDest
ADD CreateDate DATETIME
CONSTRAINT DF_DataDest_CreateDate DEFAULT (GETDATE());
Inside your package, you'll probably need to open up the destination object in your data flow to pick up the new metadata for the table, and then you'll have to click on the Mapping tab, because SSIS has to be like that. The new column should show up there, but you don't need to do anything with it, beyond making sure that SSIS knows it's there.
During your insert operations then, SSIS will not load that column, and the default value of the current date time will be populated for you.
If you add an insert stamp to the table with a default of getdate, the database will handle this for you:
alter table destionationtable add [InsertStamp] [DATETIME] NOT NULL CONSTRAINT [DF_Insert] DEFAULT (GETDATE())