5

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.

  • Are you pulling the data from the columns directly, or through a query? – RDFozz Feb 27 '17 at 14:47
  • 1
    @cXzY You're having difficulties because you're using unregistered (cookie-based) accounts. These require you to use the same device and browser each time (without clearing cookies of course). Consider creating a registered account then asking for your accounts to be merged. Once that is done, you'll be able to edit your question, leave comments, and accept an answer when the time comes. – Paul White Feb 27 '17 at 15:10
5

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

SQLNCLI

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

SQLOLEDB

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.