I would recommend using Robocopy - it can be called from a PS script and there are many possible parameters to fit almost any requirement.

The output from Robocopy also provides...

A couple more points:

1. SQL Server cannot truncate the log on the primary until the transaction has been hardened on the secondary.  If either the send queue or redo queue...

SQL Server cannot truncate the log until after the backup completes - because that is the last phase of the backup process.  Once the database has been backed up, SQL...

The recommendation is to not have more than 8 tempdb files - unless you are specifically seeing contention.  You can start here: https://docs.microsoft.com/en-US/troubleshoot/sql/performance/recommendations-reduce-allocation-contention

I see a further problem - the files obviously have timestamps associated with the file.  Processing them in 'any order' could be problematic if there could be actions/updates for the...

You don't really need SSMS to be able to get a copy of that package - it depends on how it was deployed.  For 2008 deployments you could deploy to...

To the specific question - yes, setting a commit size and batch size will reduce the impact on the transaction log and allow the log to be truncated.  Whether or...

Here is something that rubs me on a daily basis:

I hate sprinkling my code with RAISERROR just for the sake of printing status messages, because it's ugly and confusing...

Along the same lines, what techniques do people use to inject commas into, say, @@ROWCOUNT vs the FORMAT statement - FORMAT( @@ROWCOUNT ), '###,###,###,##0' ) AS RowCount

I still find...

You asked how to use WinSCP in SSIS - the assumption would be that you are currently using WinSCP manually to synchronize the folders.  Is that correct?

My next question would...

Any process that runs prior to that 'large' transaction log backup that changes a lot of data is going to cause a lot of transactions to be recorded in the...

Since this is an audit table - you could get away with just creating a new table in the new structure and then swap in the new table for the...

Are those values actually 'A1', 'A2', ...  Or was that just for the sample data?  If just for the sample data - then you are going to need a source...

How about something like this:

   With t(n)
     As (
 Select t.n
   From (Values (0), (0), (0), (0), (0), (0),...

Looks to me like you did not create a new database (prerequisite #3) and update the script to reference that new database.  The database where the script is trying to...