0

I need to import a flat file into a database table every hour, however the file itself is continuously updated by an application (file doesn't lock so that's no problem). What i need is to make sure that i'm only importing the latest data for the last hour.

So i need to make sure that there is no duplicate data in the table and only bring in the data for that hour. Hope this makes sense!! oh and sadly there is no way to change the log files to create one every hour!

Thanks

GPH
  • 1,817
  • 3
  • 29
  • 50
  • Do you have any date column in flat file which specifies when it was updated ? – praveen May 08 '13 at 08:10
  • yeah there is a date time for when a new record is logged. you think i could do a max date from the table and only update where record is greater? – GPH May 08 '13 at 08:13
  • Create a variable in SSIS as `LastRunDate` .After Flat File Source use spilt transformation and check if `FlatFlitDate >= LastRunDate` .If you have a `date time` column in destination too then populate the value for `LastRunDate` from this column . – praveen May 08 '13 at 08:17

1 Answers1

0

You can try the max date.

Or Something like this.

Delete the most recent data in a first step:

DELETE FROM [DATABASE].[dbo].[TABLE]
WHERE [DATE] > DATEADD(hour, -1, GETDATE())

and then

Update:

SELECT * FROM [DATABASE].[dbo].[TABLE]
WHERE [DATE] > DATEADD(hour, -1, GETDATE())

So you delete the most recent data and then load in everything from there again.

HTH

Inus C
  • 1,521
  • 1
  • 16
  • 23