Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jansub07
Starting Member
1 Post |
 Posted - 2014-05-26 : 05:48:42
|
| Purge and Archive using ssis packagesThe OLTP DB and Archive DB are of two separate servers. We have like hundreds of tables. Deletion is not a problem since I can issue a delete command (base on a condition), and this can be done on Execute SQL task job. I can loop through the table list for deletion and issue a execute sql job -- this is feasible. However, the archiving thing is somewhat hard to implement. For what I understood from your suggestion, I still have to create one data flow per table coz for each table it has different metadata column mappings. I cannot use OLEDB Source and destination and just loop through the table list for archive. I just wanna get all data with specific condition and transfer it to an Archive DB. We dont want to use linked server too (INSERT INTO ArchiveTable SELECT {Columns here} FROM ServerName.Databasename.dbo.OLTPTable WHERE {condition}). |
|
|
vaibhavktiwari83
Aged Yak Warrior
843 Posts |
Posted - 2014-05-28 : 09:10:44
|
| This is the basic thing which is done in SSIS. You just need to create two connection one for source and one for target. then drag a data flow task and inside the Data Flow Task drag a OLEDB source and target in source write the select query and map the fields with target table and execute the package.Vaibhav TIf I cant go back, I want to go fast... |
 |
|
|
|
|
|
Purge and Archive using ssis packages