如何使用SSIS包向含IDENTITY列的ACCOUNT表执行批量插入？

Hey there! Let's walk through how to perform bulk inserts into your ACCOUNT table (which has an IDENTITY column ID) using SSIS. I'll cover the two most common scenarios you're likely to encounter:

Scenario 1: Let SQL Server auto-generate the IDENTITY value (most common use case) #

This is the straightforward approach when you don't need to control the ID values—you let SQL Server handle incrementing it automatically. Here's how to set it up:

• Configure your data source: Whether you're pulling from a CSV, Excel file, or another database, make sure your source data either doesn't include the ID column, or you ignore it in the data flow.
• Set up the OLE DB Destination:
  1. Connect to your SQL Server database and select [dbo].[ACCOUNT] as the target table.
  2. Go to the Mappings tab: Map all columns from your source to the corresponding target columns except ID. Since ID is an IDENTITY column, SQL Server will populate it automatically during insertion.
  3. If you're using Fast Load mode (highly recommended for bulk operations due to better performance), ensure the Keep Identity option is unchecked in the Fast Load settings. This tells SQL Server to generate the ID values instead of using any provided values.

Scenario 2: Insert custom IDENTITY values (e.g., migrating existing data) #

If you need to preserve existing ID values (like when migrating data from another system), you'll need to enable IDENTITY_INSERT for the table first. Here's the step-by-step:

1. Add an Execute SQL Task before your Data Flow Task:
   • Configure it to run this SQL statement:

     SET IDENTITY_INSERT [dbo].[ACCOUNT] ON;
     

     This allows manual insertion into the ID column.
2. Configure the Data Flow Task:
   • Ensure your source data includes the ID column with the values you want to insert.
   • In the OLE DB Destination, map all columns including ID in the Mappings tab.
   • If using Fast Load mode, make sure to check the Keep Identity option—this tells SSIS to pass the custom ID values to SQL Server.
3. Add another Execute SQL Task after the Data Flow Task:
   • Run this statement to disable IDENTITY_INSERT (critical to avoid issues with future inserts):

     SET IDENTITY_INSERT [dbo].[ACCOUNT] OFF;
     

Key Notes: #

• Fast Load vs. Row-by-Row: Always prefer Fast Load for bulk inserts—it's significantly faster. Just be aware that if your table has constraints (like foreign keys or check constraints), you might need to disable them temporarily before insertion, or adjust the Fast Load settings (toggle the Check Constraints option as needed).
• Data Type Compatibility: If your source is a text file (e.g., CSV), double-check that the ID values match the numeric(18,0) data type of the target column to avoid conversion errors.
• Test with Small Batches: Before running a full bulk insert, test with a small dataset to verify that the ID column behaves as expected (auto-generated or custom values are inserted correctly).

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