代码之家 › 专栏 › 技术社区 › mattstuehler

使用向导将csv数据导入到SQL Server中-隐藏的错误消息

sql-server

mattstuehler · 技术社区 · 16 年前

我正在尝试使用SQL Server导入向导将纯文本文件导入SQL Server。

看起来这应该很简单。文本文件是以制表符分隔的,没有什么异常。

在向导中预览数据时,一切都正常。我已经检查了列映射和所有数据行,数据类型看起来都正常。

但当我运行它时,我得到的错误消息是:

Copying to [dbo].[my_table] (Error) Messages

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
 (SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task: There was an error with input column "touch4_date" (260) on input "Destination Input" (179). The column status returned was: "The value could not be converted because of a potential loss of data.".
 (SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (179)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "Destination Input" (179)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - cm_campaigns" (166) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  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 "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)

一些额外的细节……

平面文件只有22行。

在似乎导致问题的列中(“touch4_date”)-19行包含空值,3行包含以下值:

7/2/2008
8/25/2008
3/12/2009

数据库的列是日期时间,它允许空值。

其中一个奇怪的方面是平面文件包含其他列,这些列的数据格式与映射到日期时间列的“mm/dd/yyyy”相同,但这些列似乎不会导致任何错误?

有人能解释一下这个问题吗?

非常感谢!

2 回复 | 直到 16 年前

David Andres 16 年前

touch4_date列的值不能转换为表列类型。

DForck42 16 年前

尝试将平面文件导入临时表,其中tough4_date列为varchar。然后插入到实际表中,但使用case语句。

declare @string varchar(50)

set @string=''



select
    case @string 
        when '' then null
        else @string
    end as String