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
.
Best Answer
You are using
SQLOLEDB.1
which is a pre-SQL 2008 version of the driver (and deprecated), and thedate
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