 <p>Create 2 OLE DB Connection Managers. Name them Production and Archive and have them point to the correct servers and database. These CMs are what SSIS uses to push and pull data from the databases.</p> <p>Add a Data Flow Task. A DFT is the executable that will allow row by row manipulation of the data. Double click on the Data Flow Task. Once inside, add an OLE DB Source and and OLE DB Destination to the canvas. The OLE DB Source is where the data will come from while the OLE DB Destination provides the insert power. </p> <p>The logic you would want to implement is a <a href="http://stackoverflow.com/questions/15503959/ssis-foreach-through-a-table-insert-into-another-and-delete-the-source-row/15508174#15508174">Delete first</a> approach, much as I outlined in the other answer.</p> <pre><code>DELETE DF OUTPUT DELETED.* FROM dbo.DeleteFirst AS DF WHERE DF.RecordDate &gt; dateadd(y, 3, current_timestamp); </code></pre> <p>This query will delete all the rows older than 3 years <em>and</em> push them into the dataflow. In your OLE DB Source, make the following configuration changes</p> <ol> <li>change the Connection Manager from <code>Archive</code> to <code>Production</code></li> <li>change the query type from "Table or View" to "Query" </li> <li>paste your query and click the Columns tab to double check the query parsed</li> </ol> <p>Connect the OLE DB Source to the OLE DB Destination. Double click on the OLE DB Destination and configure it</p> <ol> <li>Verify the Connection Manager is the <code>Archive</code></li> <li>Ensure the Access Mode is "Table or View - Fastload" (name approximate)</li> <li>You might need to check the Retain IDs based on your table design - if you have identity column, then do check it if you want ID 10 from the production system to be ID 10 in the Archive system</li> <li>Select the actual table</li> <li>On the Mapping tab, ensure that all the columns mapped. It does this automatically by matching names so there shouldn't be a problem.</li> </ol> <p>If you do not need to span an instance, the above logic can be condensed into a single <code>Execute SQL Task</code></p> <pre><code>DELETE DF OUTPUT DELETED.* INTO ArchiveDatabase.dbo.DeleteFirst FROM dbo.DeleteFirst AS DF WHERE DF.RecordDate &gt; dateadd(y, 3, current_timestamp); </code></pre> <p>Also note with this approach that if you have identity columns you will need to provide an explicit column list and turn on and off the IDENTITY_INSERT property.</p>