如何修复“强制转换规范的无效字符值”在平面文件的日期列上？

Question

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

我的目标列是 SQL Server 2008 数据库日期类型表（不是日期时间）

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

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

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

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

Answer 1

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

显然这些日期格式之间的差异如下：

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

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

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

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

Answer 2

我的日期值错误，即我有一个针对 2022-09-31 运行的查询，这是不正确的，因为 9 月份不是 31 天的月份。将其更改为 2022-09-30 效果很好。

Answer 3

为了模拟您面临的问题，我使用 SSIS 2008 R2 和 SQL Server 2008 R2 后端创建了以下示例。该示例基于我从您的问题中收集到的内容。此示例未提供解决方案，但它可能会帮助您确定您的情况中问题可能出在哪里。

创建一个简单的 CSV 文件，其中包含两列，即订单号和订单日期。正如您在问题中提到的，两列的值都用双引号 (") 限定，行也以换行符 (\n) 结尾，日期是最后一列。下面的屏幕截图是使用 ，可以显示屏幕截图中的特殊字符 LF 表示换行。

="http://notepad-plus-plus.org/" rel="noreferrer" > src="https://i.sstatic.net/xz0yi.png" alt="Orders file">

在 SQL Server 数据库中创建了一个名为 dbo.Destination 的简单表来填充 CSV 文件数据下面给出了使用 SSIS 包创建表的脚本

CREATE TABLE [dbo].[Destination](
    [OrderNumber] [varchar](50) NULL,
    [OrderDate] [date] NULL
) ON [PRIMARY]
GO

，我使用 OLE DB 连接创建了两个连接管理器来连接到 SQL Server 数据库。平面文件 平面文件连接管理器

配置为读取 CSV 文件，设置如下所示。红色箭头表示所做的更改。

为平面文件连接管理器提供名称。浏览到 CSV 文件的位置并选择文件路径。输入双引号 (") 作为文本限定符。将标题行分隔符从 {CR}{LF} 更改为 {LF}。此标题行分隔符更改也反映了在“列”部分中。

部分中未进行任何更改。

将列名称从 Column0 更改为 OrderNumber。

src="https://i.sstatic.net/GkaC2.png" alt = src="https://i.sstatic.net/OqrZ0.png" alt="高级列 OrderNumber">

将列名称从 Column1 更改为 OrderDate 并且也更改这数据类型为日期 [DT_DATE]

平面文件连接中数据的预览 在

SSIS 包的 Control Flow 选项卡上，放置了 <代码>数据流任务。

在数据流任务中，放置了一个平面文件源和一个OLE DB 目标。

平面文件源 配置为使用 FlatFile 连接读取 CSV 文件数据经理。下面的三个屏幕截图显示了平面文件源组件的配置方式。

已配置 OLE DB 目标 组件接受来自平面文件源的数据并将其插入到名为 dbo.Destination 的 SQL Server 数据库表中。下面的三个屏幕截图显示了 OLE DB 目标组件的配置方式。

使用以下 5 中提到的步骤屏幕截图中，我在平面文件源和 OLE DB 目标之间的流上添加了一个数据查看器。

在运行包之前，我验证了表中存在的初始数据。它目前是空的，因为我使用本文开头提供的脚本创建了它。

执行了包，包执行暂时暂停，以显示从平面文件源到 OLE DB 目标的数据流数据查看器。我单击运行按钮继续执行。

包已成功执行。

平面文件源数据已成功插入到表 dbo.Destination 中。

这是表 dbo.Destination 的布局。正如您所看到的，字段 OrderDate 的数据类型为 date，并且包仍然继续正确插入数据。

这篇文章虽然不是解决方案。希望可以帮助您找出您的场景中问题可能出在哪里。