question

sqlrookie avatar image
sqlrookie asked

ARCHIVE DATA USING SSIS

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).
ssisarchivingdata-transfer
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site works by voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
@SQLShark avatar image
@SQLShark answered
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.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I would like to keep the archive database in the same server of production database.
0 Likes 0 ·
sqlrookie avatar image
sqlrookie answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
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!
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hey John Thanks for the ideas... Actually i would like to archive the data into new database and store it, like taking backup of that archive database.So that's the reason i need separate database.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.