Loding data using package

Question

Post reply

Loding data using package

daveriya

SSCrazy

Points: 2352
More actions
June 5, 2011 at 7:24 pm

#240876

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

Viewing 5 posts - 1 through 4 (of 4 total)

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

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 1

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.

BLOB EATER SSChampion Points: 14609 More actions · Answer 2

i agree. i would just have a sql task in ssis that does the yearly date check.

FMTONLY Mr or Mrs. 500 Points: 578 More actions · Answer 3

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

somet Grasshopper Points: 19 More actions · Answer 4

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 🙂