Post reply

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

  • 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.

  • February 3, 2010 at 7:42 am

    #1112892

    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/

  • February 3, 2010 at 7:49 am

    #1112898

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

  • February 3, 2010 at 8:12 am

    #1112929

    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/

  • February 3, 2010 at 8:50 am

    #1112977

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

  • February 3, 2010 at 9:41 am

    #1113017

    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/

  • February 4, 2010 at 2:00 am

    #1113538

    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.

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

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