Inserting to a table when everything is parameterized
// ok, stop, think. this can't be right.



Indeed, there's a better, cleaner, safer, faster way.

You have a DataTable in memory; you can "dump" it as-is to the destination table using a SqlBulkCopy object:

public override void CreateNewOutputRows()
{
    using (var records = new DataTable())
    using (var adapter = new OleDbDataAdapter())
    {
        adapter.Fill(records, Variables.RemoteData);

        var bulk = new SqlBulkCopy(_connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null);
        bulk.BulkCopyTimeout = 0;
        bulk.DestinationTableName = Variables.StagingTableName;
        bulk.WriteToServer(records);

        _transferredRows = records.Rows.Count;
        Output0Buffer.SetEndOfRowset();
    }
}


That fixes the first problem - you indeed don't need to be bothered with individual rows and/or column names and types, even less to build/concatenate an individual INSERT statement for every single row in the data source.



The other problem to solve is to get each iteration to run concurrently. I believe you could extract the body of the Foreach loop container into its own parameterized SSIS package, and replace it with an out-of-process Execute Package Task.