Прежде чем грохать индексы надо было прочитать это:

Optimizing Bulk Copy Performance
To bulk copy data as fast as possible, several options are available to specify how data should be bulk copied into Microsoft® SQL Server™ using the bcp utility or BULK INSERT statement, including:

Using logged and nonlogged bulk copies.
Using the bcp utility for parallel data loading.
Controlling the locking behavior.
Using batches.
Ordering data files.
For more information, see Factors Affecting Bulk Copy Performance.

--------------------------------------------------------------------------------

Note If possible, use the BULK INSERT statement rather than the bcp utility to bulk copy data into SQL Server. The BULK INSERT statement is faster than the bcp utility.


--------------------------------------------------------------------------------

Two factors determine which of these options can or should be used to increase the performance of bulk-copy operations:

Amount of existing data in the table compared to the amount of data to be copied into the table.
Number and type of indexes on the table.
Additionally, these factors depend on whether data is bulk copied into a table from a single client or in parallel from multiple clients.

Loading Data into an Empty Table from a Single Client
When loading data into an empty table from a single client, it is recommended that you specify:

The TABLOCK hint. This causes a table-level lock to be taken for the duration of the bulk-copy operation.
A large batch size, using the ROWS_PER_BATCH hint. A single batch representing the size of the entire file is recommended.
A nonlogged bulk-copy operation. Transaction log backups should not be created after performing a nonlogged operation. For more information, see Backing Up Nonlogged Operations.
Additionally, if your table has a clustered index and the data in the data file is ordered to match the clustered index key columns, bulk copy the data into the table with the clustered index already in place and specify the ORDER hint. This is significantly faster than creating the clustered index after the data is copied into the table.

If nonclustered indexes are also present on the table, drop these before copying data into the table. It is generally faster to bulk copy data into a table without nonclustered indexes, and then to re-create the nonclustered indexes, rather than bulk copy data into a table with the nonclustered indexes in place.

Loading Data into a Nonempty Table from a Single Client
When copying data into a table that has existing data, the recommendation to perform the bulk copy operation with the indexes in place depends on the amount of data to be copied into the table compared to the amount of existing data already in the table. As the percentage of data to be copied into the table increases (based on the amount of existing data in the table), the faster it is to drop all indexes on the table, perform the bulk copy operation, and then re-create the indexes after the data is loaded.

As a rough guide, here are suggested figures for the amount of data to be added to a table for various types of indexes. If you exceed these percentages, you may find it faster to drop and re-create the indexes.

Indexes Amount of data added
Clustered index only 30%
Clustered and one nonclustered index 25%
Clustered and two nonclustered indexes 25%
Single nonclustered index only 100%
Two nonclustered indexes 60%

Получается, что начиная с 4-5-ого дня вставки (если у вас кластерный и два некластерных), алгоритм по которому происходит удаление инжексов, а затем вставка данных и создание индексов будет не оптимальным.

2 Nikolay. Да никто не отрицает, что-то, что я привел справедливо для BULK INSERT, но человеку советовали использовать DTS что впрочем и вы советуете.

2 Nikolay & VVG Вопрос. Как по вашему через что работает DTS?
Так вот я вам отвечу DTS как раз и использует BULK COPY (для этого я и привел эту информацию).
Поэтому утверждать, что DTS отдыхает в отличие от Bulk insert я бы не стал (те же яйца, только вид сбоку), надо правильно настраивать пакет.