SSIS / SQL Server / Performance Tuning

Issue: DATE column as DT_WSTR in SSIS in lookup February 20, 2017

It appears that OLEDB provider for SQL Server is incorrectly mapping DATE column from SQL SERVER into DT_WSTR (wide string). Same query using SQL native provider 11.1 (SQLNCLI11.1) is correctly mapped as DT_DBDATE column.

Connection String for SQL Native 11.1 :

Data Source=(local);Initial Catalog=Test_DB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;

Connnection String for OLEDB Provider for SQL Server

Data Source=(local);Initial Catalog=Test_DB;Provider=SQLOLEDB;Integrated Security=SSPI;Auto Translate=False;

If you have to use OLEDB provider for other benefits then you must type cast data using Data Conversion component. In my case I was trying to use it with LOOKUP component and therefore could not change datatype or use data conversion component or change column metadata in advance properties, I ended up using SQL Native client 11.1 provider for my problem but for OLEDB Source component I would prefer using Data Conversion component instead.

Hopefully it will help someone. Happy SSIS’ing