sql-server - 从 Excel 导入到 SQL Server 2005 时出现截断错误

Question

长话短说，我将一堆 Excel 文档逐一导入，并使用导入/导出向导将它们导入到 SQL Server 2005 中的数据库中。

这是一份报告(所有未显示的进程均表示“成功”)。有什么办法可以让我忽略截断错误吗？我用谷歌搜索了一无所获，或者至少在我的版本中没有。

- Executing (Success)

- Copying to [Datadev].[dbo].[Sheet0$] (Error)
  Messages
  * Error 0xc020901c: Data Flow Task: There was an error with output

column "Value Meaning Description" (234) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

  * Error 0xc020902a: Data Flow Task: The "output column "Value

Meaning Description" (234)" failed because truncation occurred, and the truncation row disposition on "output column "Value Meaning Description" (234)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

  * Error 0xc0047038: Data Flow Task: SSIS Error Code

DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sheet0$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

  * Error 0xc0047021: Data Flow Task: SSIS Error Code

DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

  * Error 0xc0047039: Data Flow Task: SSIS Error Code

DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. (SQL Server Import and Export Wizard)

  * Error 0xc0047021: Data Flow Task: SSIS Error Code

DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

- Post-execute (Success)
  Messages
  * Information 0x402090df: Data Flow Task: The final commit for the

data insertion has started. (SQL Server Import and Export Wizard)

  * Information 0x402090e0: Data Flow Task: The final commit for the

data insertion has ended. (SQL Server Import and Export Wizard)

- Cleanup (Success)
  Messages
  * Information 0x4004300b: Data Flow Task: "component "Destination -

Sheet0$" (323)" wrote 210 rows. (SQL Server Import and Export Wizard)

Answer 1

当出现截断错误时，我会插入 8 个虚拟行。每个单元格都包含长度 > 256 的垃圾文本。这会强制检测到的数据类型为 varchar(max) 而不是 varchar(256)。如果一行是数字列，我必须用数字(例如 0)填充它，如果它是日期，我必须填写一个虚拟日期，否则这些列将导入空数据。

导入后我会删除这些垃圾行。