Hello, I have a task to archive old data based on date( the data older than 2012). I was thinking like creating a new database which will be the replica of the source database but with archived data and deleting that data from source database. I tried with ssis Transfer SQL Objects task but this will transfer all the data i was not able to mention condition for the data. Please need suggestions. (T-SQL would be fine for me if its not possible through ssis).
You could skip SSIS and create a linked server, then write a TSQL script to move the data to the linked "archive" server and then delete from source. Then just schedule this to run every night. Alternately using SSIS you could use a DFT to take the data from your source db (based on a SQL statement which includes your WHERE clause) move it to the destination and write a sql task to delete it from source. Hope that helps.
Thanks for the suggestion. I got a similar idea to implement this without SSIS. Please let me know is it a good way to archive. 1.will create a new database say DB_ARCHIVE which will be the replica of the production database. 2.DB_ARCHIVE database will have only the table structure.
3.By using the script BCP OUT (BULK COPY PASTE), we can extract the data from the respective tables based on condition( datepart(year,"datecolumn" < 2013)) into flat file. 4.Again by using same BCP IN Script, will import the data from the flat file into the tables in DB_ARCHIVE database. 5.After this i will delete the archived data from production database through script.
Just curious, is there a reason as to why it needs it's own database? You could very easily create the table structure in the same database and then just move the data with straight forward T-SQL. Is there a problem you're trying to solve by putting it into a separate database? This would be a relatively simple implementation and would help prevent adding extra complexities such as a SSIS package or BCP process. In regards to your original question, @sqlshark is correct in that the easiest way with SSIS is to use a Data Flow Task (DFT) and specify your dynamic query in the data source. Once the data has flowed to the destination, you can then use a SQL task to delete the data accordingly. Note: There are many ways to accomplish this. SSIS, BCP, Linked Servers, etc are all valid solutions. Hope this helps!