SSIS迁移SQL Server数据至Access：目标表预清空失败求助

Hey there, I’ve dealt with this exact frustration when working on SSIS migrations to Access—since Access doesn’t support the TRUNCATE TABLE command we’re used to in SQL Server, we need to use alternative approaches. Here are the most reliable methods to empty your target table before running your data flow:

Method 1: Use DELETE Statement (Basic Table Clear) #

This is the simplest drop-in replacement for TRUNCATE when you just need to remove all rows, and don’t care about resetting AutoNumber (identity) column seeds.

• Add an Execute SQL Task before your Data Flow Task in the SSIS control flow.
• Configure it to use your Access connection manager.
• Set SQLSourceType to Direct Input, then paste this SQL:

  DELETE FROM YourTargetTableName;
  

• Run the task—it will remove all rows from the table.

Note: This won’t reset AutoNumber columns, so new rows will continue incrementing from the last used value.

Method 2: Clear Rows + Reset AutoNumber Column #

If you need to reset the AutoNumber seed (matching the behavior of TRUNCATE TABLE in SQL Server), combine the DELETE with an ALTER TABLE command to reset the counter:

1. First, run the DELETE statement from Method 1 to empty the table.
2. Add a second Execute SQL Task (or use a single task with multiple statements separated by semicolons) to run:

   ALTER TABLE YourTargetTableName ALTER COLUMN YourAutoNumberColumn COUNTER(1, 1);
   

Replace YourAutoNumberColumn with the name of your AutoNumber field. This resets the seed to start at 1 again for new inserts.

Method 3: Drop and Recreate the Table (For Large Datasets or Schema Resets) #

If you’re working with a very large table (where DELETE might be slow) or need to reset the entire table schema, dropping and recreating the table can be faster:

• In an Execute SQL Task, run the drop command first:

  DROP TABLE YourTargetTableName;
  

• Then run the CREATE TABLE statement that matches your target table’s schema (include all columns, data types, constraints, and indexes):

  CREATE TABLE YourTargetTableName (
      ID AUTOINCREMENT PRIMARY KEY,
      CustomerName VARCHAR(100) NOT NULL,
      OrderDate DATE,
      TotalAmount DECIMAL(10,2)
  );
  

Warning: Make sure your CREATE TABLE statement exactly matches the original table’s structure—otherwise, your Data Flow Task might fail due to schema mismatches.

Key Setup Tips for Execute SQL Task #

• Ensure your connection manager uses the correct Access driver: Use Microsoft Access Driver (.accdb)* for .accdb files, or the older Microsoft Access Driver (.mdb)* for legacy databases.
• If you’re running multiple SQL statements in one task, make sure your Access connection allows batch execution (check the connection manager’s properties if you run into errors).

Hope these solutions get your SSIS migration running smoothly! If you hit any issues with syntax or connection setup, feel free to follow up.

内容的提问来源于stack exchange，提问作者nonslearn