Excel和SQL之间的SSIS日期时间转换问题 [英] SSIS datetime conversion issue between excel and sql
问题描述
如果我再把头撞在桌子上1次，我可能会永远消失。
if I bang my head on my desk 1 more time I might be gone forever.
来源：Excel
数据：2012-10-05T11：14：00
Data: 2012-10-05T11:14:00
转化：
2012-10- 05T11：14：00在Excel中转换为字符串[DT_STR]
2012-10-05T11:14:00 in Excel to string [DT_STR]
目标：SQL
列数据类型：datetime
Column Data Type: datetime
我一直收到以下错误：
[PostBindInspectionsBucket [20]]错误：SSIS错误代码DTS_E_OLEDBERROR。发生OLE DB错误。错误代码：0x80004005。
OLE DB记录可用。源： Microsoft SQL Server本机客户端10.0结果：0x80004005说明：强制转换规范的字符值。
[PostBindInspectionsBucket [20]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid character value for cast specification".
[PostBindInspectionsBucket [20]]错误：存在一个输入 OLE DB目标输入（33）上的输入列 RequestTimestamp的副本（112）错误。返回的列状态为：由于潜在的数据丢失，无法转换该值。。
[PostBindInspectionsBucket [20]] Error: There was an error with input column "Copy of RequestTimestamp" (112) on input "OLE DB Destination Input" (33). The column status returned was: "The value could not be converted because of a potential loss of data.".
[PostBindInspectionsBucket [20]]错误：SSIS错误代码DTS_E_INDUCEDTRANSFORMFAILUREONERROR 。 输入 OLE DB目标输入（33）失败，因为发生了错误代码0xC0209077，并且在输入 OLE DB目标输入（33）上的错误行配置指定出错。指定组件的指定对象发生错误。在此之前可能会发布错误消息，并提供有关失败的更多信息。
[PostBindInspectionsBucket [20]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (33)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (33)" 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.
[SSIS.Pipeline]错误：SSIS错误代码DTS_E_PROCESSINPUTFAILED。处理输入 OLE DB目标输入（33）时，组件 PostBindInspectionsBucket（20）上的ProcessInput方法失败，错误代码为0xC0209029。所标识的组件从ProcessInput方法返回了错误。该错误是特定于组件的，但该错误是致命的，将导致数据流任务停止运行。在此之前可能会发布错误消息，并提供有关失败的更多信息。
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "PostBindInspectionsBucket" (20) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (33). 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.
我在做什么错？来自Excel的格式是有效的日期时间格式。
What am I doing wrong? the format that's coming from Excel is a valid datetime format.
我创建了一个表，该表的列类型为 datetime，然后将其粘贴为 2012-10- 05T11：14：00，效果很好。
I created a table with a column of type "datetime" and then I pasted in it "2012-10-05T11:14:00" and it worked fine.
我还运行了
SELECT ISDATE('2012-10-05T11:14:00')
返回1，表示这是SQL中的有效日期时间。
and it's returning 1, which means it's a valid datetime in SQL.
任何人都知道可能是什么问题吗？
Anyone know what could be the issue?
推荐答案
这里的问题是 T。
我从经过的字符串中删除了T。
I removed the T from the string it went through.
这篇关于Excel和SQL之间的SSIS日期时间转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
