Dear team i am loading the data(tables) from source(Oracle) to destionation(Sql server(2008))using SSIS packges.In Few tables having huge amount of data (transcationaldata).so my destination Specified Duration(example month or 3 months wise)data move to another table on same or different database for My Reporting Pupose. whenever i need this data i will fire the query on this table. right now i am doing this process manually on every month.
I need to do the Archiving Process (using SQL Schduler jobs )
you can create a procedure which will archive the required data from tables to your destination database tables based on timeperiod (3 months, 6 months etc). Then add a sql agent job in sql server agent to call and execute these procedures. Then set a schedule for the jobs to make them execute automatically as per the set frequency