SSIS - SQL Server datetimeoffset(0) destination column recognized as DT_WSTR

Question

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

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.

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

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

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".     

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))

This yielded the following result:

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

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.

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

Thanks.

Answer 1

Quick Answer: Set DataTypeCompatibility to 0

I noticed in Connection Manager for my SQL Server Native Client 11.0 (OLEDB) connection, clicking on "All", then under the SQLNCLI11.1 section there's a value DataTypeCompatibility which was set to "80". 80 is code for SQL Server 2000 compatibility, well before they introduced TimeStampOffset (or in my case DT_DBDATE and DT_DBTIME2 types). I tried setting compatibility to 130, then 100, but "Test Connection" failed.

At https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client?view=sql-server-2017 there's a table, specifying information about this value

DataTypeCompatibility SSPROP_INIT_DATATYPECOMPATIBILITY Specifies the mode of data type handling to use. Recognized values are "0" for provider data types and "80" for SQL Server 2000 data types.

Changing the value to 0, then refreshing all of my connections using the OLEDB connection manager seems to have done the trick - now all my database's types are recognized rather than forcing it to nvarchar/DT_WSTR

Answer 2

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.

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.

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.

Answer 3

This sounds like the OLEDB source metadata is out of sync with the changes you made on the flat file connection manager. The quickest fix it would be to recreate the OLEDB source, but don't do that quite yet.

SSIS is not going to like that standard ISO format for the date. If you remove the "T" in the middle and the "Z" at the end it be ok. i.e.

2017-02-06 22:07:09

Because of this conversion issue in SSIS, the connection manager will probably fail in converting the string to datetimeoffset. So you will need to configure it as a string and then fix it's value in a derived column:

(DT_DBTIMESTAMPOFFSET, 0) REPLACE(REPLACE( [DWValidFrom] , "T", " " ), "Z", "")

Hope that helps, m