SQL Server Import/Export Data Wizard vs DTExec for Excel Dates

Question

I'm trying to create an Excel spreadsheet using the latest SSDT inside Visual Studio 2015 as a SQL Server 2014 project/package. I first used the SQL Server Import/Export Data Wizard to run and create a DTS package that issues a simple query to retrieve data from a SQL Server database table which includes a DATETIME columnn and export this to Excel, preserving the DATETIME column as a DATE in Excel.

Running the DTS package during the wizard correctly creates the Excel file with the column value as a DATE. But when I re-run the DTS package that the wizard saved to the file system using Visual Studio 2015 (using 32-bit mode) or ANY 32-bit version of the DTExec utility, the Excel file is always created with the column value as TEXT. The Excel sheet is created using the 'DateTime' type and the internal SSIS type is DT_DBTIMESTAMP. I'm using the Microsoft.ACE.OLEDB.12.0 32-bit driver as I have Office 2016 32-bit installed on my developer workstation (although likely will be using the 64-bit version on production server).

Any thoughts on why the difference occurs when running it using the wizard vs. running it in Visual Studio or using DTExec? This is driving me crazy!

Or does anyone have any different approaches to forcing this column to be a DATE in Excel?

Thanks!

Bryan

Answer 1

Exporting date records to excel via SSIS will always be treated as TEXT, unless you have dummy records in your excel sheet already, then import to that excel right after dummy rows. Assume you have dummy rows set to DATE format