解决从CSV批量导入SQL Server时的Bulk load data conversion error错误

From your description, the root cause is definitely your manually written format file—there are critical column mapping mismatches and terminator configuration errors that are causing SQL Server to misparse your CSV data, especially the TIMESTAMP column.

Key Issues in Your Original Format File #

1. Column Count & Mapping Offset
   Your flow_data_test table has 21 columns (with id as the first column), but your format file starts with 20 (declaring only 20 columns) and completely omits the id column. This shifts all subsequent column mappings by one position: SQL Server tries to map the CSV's 20th field (the datetime value) to your table's 20th column (DIRECTION, a SMALLINT type), which triggers the type mismatch error.

2. Incorrect Field Terminators
   Your CSV uses commas as separators, but most columns in your format file use an empty string "" as the terminator. SQL Server can't split the CSV fields correctly with this setup. Only the last column should use "\n" as the terminator; all others need ",".

3. Minor Type Naming Inconsistency
   For INT type columns like L4_SRC_PORT and L4_DST_PORT, using SQLINT4 (the explicit format file type for SQL INT) is more clear than SQLINT (though both work, consistency helps avoid confusion).

Corrected Format File #

9.0
21
1 SQLCHAR 0 25 "," 1 id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 25 "," 2 USERNAME SQL_Latin1_General_CP1_CI_AS
3 SQLBIGINT 0 8 "," 3 FIRST_SWITCHED ""
4 SQLBIGINT 0 8 "," 4 LAST_SWITCHED ""
5 SQLBIGINT 0 8 "," 5 IN_BYTES ""
6 SQLBIGINT 0 8 "," 6 IN_PKTS ""
7 SQLBIGINT 0 8 "," 7 INPUT_SNMP ""
8 SQLBIGINT 0 8 "," 8 OUTPUT_SNMP ""
9 SQLBIGINT 0 8 "," 9 IPV4_SRC_ADDR ""
10 SQLBIGINT 0 8 "," 10 IPV4_DST_ADDR ""
11 SQLSMALLINT 0 2 "," 11 PROTOCOL ""
12 SQLSMALLINT 0 2 "," 12 SRC_TOS ""
13 SQLINT4 0 4 "," 13 L4_SRC_PORT ""
14 SQLINT4 0 4 "," 14 L4_DST_PORT ""
15 SQLSMALLINT 0 2 "," 15 FLOW_SAMPLER_ID ""
16 SQLBIGINT 0 8 "," 16 IPV4_NEXT_HOP ""
17 SQLSMALLINT 0 2 "," 17 DST_MASK ""
18 SQLSMALLINT 0 2 "," 18 SRC_MASK ""
19 SQLSMALLINT 0 2 "," 19 TCP_FLAGS ""
20 SQLSMALLINT 0 2 "," 20 DIRECTION ""
21 SQLDATETIME 0 26 "\n" 21 TIMESTAMP ""

What's Fixed? #

• Added the missing id column mapping (first line) to align with your table structure.
• Set the correct column count (21) at the top of the file.
• Changed all intermediate column terminators to "," to match your CSV's comma-separated format.
• Used SQLINT4 for INT type columns for clarity.
• Ensured every line's target column number (the 5th value in each line) exactly matches the table's column order.

Testing & Optimization Tips #

• First test with a small subset of your data (like the 2 rows you provided) to confirm the fix works before processing the full 1.5 billion rows.
• Your BATCHSIZE = 1000000 is a good choice for large datasets, but you can adjust it based on your server's memory and I/O performance if needed.
• Verify your CSV file uses the SQL_Latin1_General_CP1_CI_AS codepage to avoid character encoding issues.

Since you confirmed the datetime format works with direct INSERT statements, this fix should resolve the bulk insert error without needing intermediate tables.

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