如何修复平面文件中日期列上的“演员规范的无效字符值"? [英] How do I fix 'Invalid character value for cast specification' on a date column in flat file?

问题描述

我有一个 CSV 文件，其中 {LF} 分隔每一行和一个日期列，日期格式为 "12/20/2010"(包括引号)

I have a CSV file with a {LF} delimiting each row and a date column with the date format as "12/20/2010" (including quotation marks)

我的目标列是一个日期类型(不是日期时间)的 SQL Server 2008 数据库表

My destination column is a SQL Server 2008 database table of type date (not datetime)

在我的平面文件连接管理器中，我已将日期列配置为数据类型 date [DT_DATE]，TextQualified 设置为 true，列分隔符为 >{LF}(它是每行的最后一列).我将文本限定符设置为 "

In my Flat File Connection Manager, I have configured the date column to be data type date [DT_DATE] with TextQualified set to true and the column delimiter as {LF} (it is the last column on each row). I have the text qualifier set to "

当我尝试将其加载到 OLE 目标时，出现以下错误

When I try to load this into an OLE Destination I get the following error

[TRN_DORPS [760]] 错误:SSIS 错误代码 DTS_E_OLEDBERROR.发生 OLE DB 错误.错误代码:0x80004005.OLE DB 记录可用.来源:Microsoft OLE DB Provider for SQL Server" Hresult:0x80004005 描述:转换规范的字符值无效.".[TRN_DORPS [760]] 错误:输入列CYCLE_DATE"(874) 在输入OLE DB 目标输入"(773) 上出现错误.返回的列状态为:由于潜在的数据丢失，无法转换该值.".

[TRN_DORPS [760]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.". [TRN_DORPS [760]] Error: There was an error with input column ""CYCLE_DATE"" (874) on input "OLE DB Destination Input" (773). The column status returned was: "The value could not be converted because of a potential loss of data.".

如果我附加数据查看器，管道中的值为 2010-12-20 00:00:00.0000000 - 这个时间组件是什么导致了问题?我尝试使用 (DT_DATE)(DT_DBDATE)[CYCLE_DATE] 去除时间分量，但无济于事，因为它在管道中保持不变

If I attach a data viewer, the value in the pipeline is 2010-12-20 00:00:00.0000000 - is this time component what's causing the problem? I try to strip out the time component with (DT_DATE)(DT_DBDATE)[CYCLE_DATE] but to no avail as it stays the same in the pipeline

推荐答案

我最终能够通过将平面文件连接中的列类型设置为数据库日期 [DT_DBDATE]"类型来解决该解决方案

I was ultimately able to resolve the solution by setting the column type in the flat file connection to be of type "database date [DT_DBDATE]"

显然这些日期格式之间的区别如下:

Apparently the differences between these date formats are as follow:

DT_DATE由年、月、日、和小时组成的日期结构.

DT_DATE A date structure that consists of year, month, day, and hour.

DT_DBDATE由年、月和日组成的日期结构.

DT_DBDATE A date structure that consists of year, month, and day.

DT_DBTIMESTAMP由年、月、时、分、秒、小数组成的时间戳结构

DT_DBTIMESTAMP A timestamp structure that consists of year, month, hour, minute, second, and fraction

通过将列类型更改为 DT_DBDATE，问题得到解决 - 我附加了一个数据查看器，CYCLE_DATE 值现在只是12/20/2010"，没有时间组件，这显然解决了问题.

By changing the column type to DT_DBDATE the issue was resolved - I attached a Data Viewer and the CYCLE_DATE value was now simply "12/20/2010" without a time component, which apparently resolved the issue.

这篇关于如何修复平面文件中日期列上的“演员规范的无效字符值"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！