Recording date/time of import of data from csv file into database table

Question

Recording date/time of import of data from csv file into database table

JayK

SSCrazy

Points: 2679
More actions
February 3, 2010 at 7:36 am

#141079

Hi,
I currently have an SSIS package set up to import a csv file of text data into a SQL 2008 database table on a daily basis.
What I want, however, is to add a Import_Timestamp column to the table that the file gets imported into which will record the date/time of the import of each row.
I have been trying differnt things this afternoon but no luck so far.
Any help appreciated!
JK.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

You should add the column to the table.

You may want to add two columns, one to store the Date the File Created and another to store the Date Modified of the File System Object.

Create package level variables to store these values.

In VB.NET add a referenece to the File System Object and add code to update the Date Created and DateModified variables.

Use the variable(s) as input paramater(s) in a SQL Task.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JayK SSCrazy Points: 2679 More actions · Answer 2

Thanks for reply.

I know how to generate the datetime of the import - the problem is where in the SSIS package can I add the task so that only the rows that have been imported on this iteration will get the current system time,

I was looking at it working like this:

The table has an extra column called Import_Timestamp.

The csv file is imported via a Data Flow task

Then an update is made to the table setting the Import_Timestamp column value to be the current system time (GETDATE()) for each row just imported

But I am not sure where to place the Timestamp update command so that only the rows just imported get updated - and not previously imported data....

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Sounds like you need to store the value of the column that identifies what records to update in a package variable and then use variabole as an input parameter in a SQL Task that performs the update...

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jdgonzalez@jdandrachel.com Hall of Fame Points: 3161 More actions · Answer 4

You could always just add a column to your table with a default value of getdate().

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

If you want all of the records of the Date Column to contain the exact same value for each date imnported then you could define a variable in the SQL Task and assign it the Value GetDate().

Then incluse the variable namne in your Insert Statement.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JayK SSCrazy Points: 2679 More actions · Answer 6

Thanks for the replies - yes I got it working my setting the Default value of the column to GETDATE() - so now when I import the text files through my SSIS package this column is updated with the time of the import.

I had been setting the GETDATE() function as the computed column - this is why I was not getting the result I was looking for.