如何在平面文件的日期列上修复“用于转换说明的无效字符值”? [英] How do I fix 'Invalid character value for cast specification' on a date column in flat file?
问题描述
我有一个带有{LF}分隔每行的CSV文件和一个日期列,其日期格式为 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)
我的目标列是日期类型(不是datetime)的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记录。来源: SQL Server的Microsoft OLE DB提供程序结果:0x80004005说明:强制转换规范的字符值。。
[TRN_DORPS [760 ]]错误:输入 OLE DB目标输入(773)上的输入列 CYCLE_DATE(874)出错。返回的列状态为:由于潜在的丢失,无法转换该值数据。。
[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_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值现在只是 2010年12月20日,没有时间部分,显然可以解决问题。
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屋!