SSIS - SQL Server datetimeoffset(0) 目标列被识别为 DT_WSTR [英] SSIS - SQL Server datetimeoffset(0) destination column recognized as DT_WSTR

问题描述

我们以平面文件的形式获得数据.我们要存储在名为 DWValidFrom 的目标列中的日期列具有以下格式:

We get data delivered to us in a flat file. A date column we want to store in a destination column called DWValidFrom has the following format:

2017-02-06T22:07:09Z

在使用平面文件连接管理器的 SSIS 中，我将所述列的数据类型设置为 DT_DBTIMESTAMPOFFSET.检查连接管理器的列和预览页面中的数据时，它正确地向我们显示.

In SSIS using a Flat File Connection Manager, I set the datatype of said column to DT_DBTIMESTAMPOFFSET. It correctly shows us when checking the data in the Columns and Preview pages of the Connection Manager.

在 SQL Server 中，我创建了目标表，并将 DWValidFrom 列定义为 datetimeoffset(0):

In SQL Server, I created the destination table, and defined the DWValidFrom column as datetimeoffset(0):

[DWValidFrom] [datetimeoffset](0) NOT NULL,

当我尝试在 OLE DB Destination 对象中设置映射时，该对象已设置为相关的 SQL Server 表，SSIS 没有它，并抛出以下错误:

When I attempt to set the mappings in the OLE DB Destination object, which has been set to the SQL Server table in question, SSIS won't have it, and throws the following error:

The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBTIMESTAMPOFFSET" and "DT_WSTR" for "DWValidFrom".     

怀疑我的区域设置有问题，我在 Management Studio 中发出以下查询以确保日期格式不会改变:

Suspecting something off with my regional settings, I issued the following query in Management Studio to ensure the format of the date wouldn't change:

SELECT CAST('2017-02-06T22:07:09Z' AS datetimeoffset(0))

结果如下:

2017-02-06 22:07:09 +00:00

为什么 SSIS 无法识别列的正确数据类型?我没有设置任何其他转换或表达式，所以我很困惑为什么 SSIS 不允许我推送有效的 datetimeoffset.

Why is SSIS not recognizing the column's proper data type? I do not have any other conversions or expressions set, so I'm confused as to why SSIS won't allow me to push a valid datetimeoffset.

我们使用的是 SQL Server 2014、Visual Studio 2015.

We're using SQL Server 2014, Visual Studio 2015.

谢谢.

推荐答案

问题似乎是 OLEDB 目标无法将 datetimeoffset 识别为有效的列格式.尽管 SQL Server 和 SSIS 中的一切都在推送一个完全有效的日期时间，但 OLEDB 目标不会有任何它.

The issue seemed to be that the OLEDB destination does not recognize datetimeoffset as a valid column format. Despite everything working in SQL Server and SSIS pushing a datetime that would be perfectly valid, the OLEDB destination wouldn't have any of it.

我考虑过使用 SQL Server 目标，但因为目标服务器与我们开发的服务器不同，所以这也不是一个选项.

I considered using a SQL Server destination, but because the target server is a different server than the one we develop on, that wasn't an option either.

我们的解决方法是使用日期时间作为数据类型来格式化列，这会导致我们丢失时区信息，但由于所有日期都是 UTC，我们真的不会错过任何数据.

The fix for us was to instead format the columns using datetime as a datatype, which causes us to loose the timezone info, but because all of the dates were UTC, we really don't miss any data.

这篇关于SSIS - SQL Server datetimeoffset(0) 目标列被识别为 DT_WSTR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！