sql-server - 将 DT_DBTIMESTAMP2 类型的列映射到 SQL Server 中的 datetime2 时，发生转换规范错误的无效字符值

Question

我正在创建一个 SSIS 包以将 CSV 文件导入到表格中。目前我正在导入一个平面文件，创建一个派生列，执行数据转换，然后将其加载到数据库。

封装数据流

我遇到的问题是在尝试将 DT_DBTIMESTAMP2 类型的列转换为 datetime2 时抛出以下错误:

[OLE DB Destination [78]] 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.".
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid character value for cast specification.".

我的数据格式是:

20-NOV-18 06.09.43.928460000 PM

在我的派生列中，我使用了以下表达式，它似乎给出了我正在寻找的结果:

"20" + SUBSTRING(DSTAMP,8,2) + "-" + 
    (SUBSTRING(DSTAMP,4,3) == "JAN" ? "01" : 
     SUBSTRING(DSTAMP,4,3) == "FEB" ? "02" : 
     SUBSTRING(DSTAMP,4,3) == "MAR" ? "03" : 
     SUBSTRING(DSTAMP,4,3) == "APR" ? "04" : 
     SUBSTRING(DSTAMP,4,3) == "MAY" ? "05" : 
     SUBSTRING(DSTAMP,4,3) == "JUN" ? "06" : 
     SUBSTRING(DSTAMP,4,3) == "JUL" ? "07" : 
     SUBSTRING(DSTAMP,4,3) == "AUG" ? "08" : 
     SUBSTRING(DSTAMP,4,3) == "SEP" ? "09" : 
     SUBSTRING(DSTAMP,4,3) == "OCT" ? "10" : 
     SUBSTRING(DSTAMP,4,3) == "NOV" ? "11" : 
     SUBSTRING(DSTAMP,4,3) == "DEC" ? "12" : " ") 
+ "-" + LEFT(DSTAMP,2) + " " + SUBSTRING(DSTAMP,11,2) + ":" +
SUBSTRING(DSTAMP,14,2) + ":" + SUBSTRING(DSTAMP,17,2) + "." +
SUBSTRING(DSTAMP,20,7)

我设置了一个数据查看器，它似乎给了我想要的结果:

数据查看器输出

我浏览了很多帖子来寻找答案，但没有找到适合我的情况的帖子。在失败之前，包正在通过 OLE DB 目标。

我擦洗了输入文件以查找异常，所有行都是正确的。

认为另一双眼睛可以提供帮助，非常感谢任何建议!

Answer 1

尝试在派生列内转换为 (DT_DBTIMESTAMP2):

(BT_DBTIMESTAMP2)("20" + SUBSTRING(DSTAMP,8,2) + "-" + 
(SUBSTRING(DSTAMP,4,3) == "JAN" ? "01" : 
 SUBSTRING(DSTAMP,4,3) == "FEB" ? "02" : 
 SUBSTRING(DSTAMP,4,3) == "MAR" ? "03" : 
 SUBSTRING(DSTAMP,4,3) == "APR" ? "04" : 
 SUBSTRING(DSTAMP,4,3) == "MAY" ? "05" : 
 SUBSTRING(DSTAMP,4,3) == "JUN" ? "06" : 
 SUBSTRING(DSTAMP,4,3) == "JUL" ? "07" : 
 SUBSTRING(DSTAMP,4,3) == "AUG" ? "08" : 
 SUBSTRING(DSTAMP,4,3) == "SEP" ? "09" : 
 SUBSTRING(DSTAMP,4,3) == "OCT" ? "10" : 
 SUBSTRING(DSTAMP,4,3) == "NOV" ? "11" : 
 SUBSTRING(DSTAMP,4,3) == "DEC" ? "12" : " ") 
 + "-" + LEFT(DSTAMP,2) + " " + SUBSTRING(DSTAMP,11,2) + ":" +
SUBSTRING(DSTAMP,14,2) + ":" + SUBSTRING(DSTAMP,17,2) + "." +
SUBSTRING(DSTAMP,20,7))