June 5, 2011 at 7:24 pm
hi
i am loading data from staging area to db,the requirement is that the db should maintain only 1 year of data and should truncate other data,How to accomplish this things.
Thanks
June 6, 2011 at 1:56 am
Hello,
You could add a "Created" date column to your (fact) table with a default value set to getDate().
Example
CREATE TABLE myTable (
column1,
etc...,
[Created] [datetime] not null,
...rest of table definition.
ALTER TABLE myTable ADD DEFAULT (getDate()) FOR myTable
That way, every record that gets inserted will be timestamped.
You can then define a SQL Server Agent job that removes all records with a Created value older than 1 year.
This a possible way to go.
Franky L.
June 6, 2011 at 4:17 am
i agree. i would just have a sql task in ssis that does the yearly date check.
June 6, 2011 at 11:56 pm
Hi,
how can we perform this using SSIS.
i think:
Connect to a Data source and using Execute SQL Task With Command INSERT INTO dstn_table FROM src_Table WHERE DATEDIFF(days, [OrderedDate], getdate()) <365
thanks
Jeevan
June 7, 2011 at 4:25 am
Hi there,
i think there are two different thinks you may ask about.
1) you want to hold just one year of history in your DWH and work with all data you recieved from source system.
in this case you realy have to have or a timestamp for each of your records(create or update depending on what you need) or a kind of load_id and a table with metadata about each load. Then just add a package deleting records from your tabel that are outdated.
2) you don't hold history and you want to work only with a subset of data your source system can provide - in that case use some sort of filter like WHERE... or my favorite for this case - read stage trough view that will filter just data you need
or do both at once 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply