SSIS changes SQL Server source data type from date to WSTR

Question

I want to work with a table from SQL Server in an SSIS data flow. One of the columns of the table is date type. When I import it to SSIS using OLE DB source component, SSIS changes the metadata of this column to WSTR.

Do you have any idea how to fix it? I think that it never happened before. I know I can force SSIS to change the data type in advanced editor, but shouldn't SSIS "know" that this is date type column and convert it into DT_DATE?

I use SQLOLEDB.1 as the provider. It's SQL Server 2012 and the data type of the column is just date.

Answer 1

You are using SQLOLEDB.1 which is a pre-SQL 2008 version of the driver (and deprecated), and the date data type was introduced in SQL 2008.

I suspect the provider isn't detecting the data type as it should, and you should probably switch to the more recent SQL Native client provider.

To illustrate this point, I created an SSIS package with OLE DB connection managers using both SQLNCLI11.1 and SQLOLEDB.1. The Data Flows will both use a query of SELECT CAST('2017-02-27' AS date) AS DateType;

Looking at the metadata, we see SQLNCLI correctly determines the date datatypes

The SQLOLEDB is unable to determine the date datatype and falls back to the "safe" type of unicode string