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.
我正在尝试使用Visual Studio 2015中最新的SSDT作为SQL Server 2014项目/包创建一个Excel电子表格。我首先使用SQL Server导入/导出数据向导来运行和创建一个DTS包，它发出一个简单的查询，从一个包含DATETIME columnn的SQL Server数据库表中检索数据，并将其导出为Excel，将DATETIME列保存为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).
在向导中正确运行DTS包，以列值作为日期创建Excel文件。但是，当我重新运行DTS包时，使用Visual Studio 2015(使用32位模式)或任何32位版本的DTExec实用程序将向导保存到文件系统中，Excel文件总是以列值作为文本创建。Excel表是使用“DateTime”类型创建的，内部SSIS类型是DT_DBTIMESTAMP。我正在使用microsoft.ac . oledb .12.0 32位驱动程序，因为我在开发人员工作站安装了Office 2016 32位驱动程序(尽管可能会在生产服务器上使用64位驱动程序)。
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!
在使用向导和在Visual Studio或使用DTExec运行时，您对为什么会出现这种差异有什么看法?这简直快把我逼疯了!
Or does anyone have any different approaches to forcing this column to be a DATE in Excel?
或者有人有什么不同的方法来强迫这个专栏成为Excel中的日期吗?
Thanks!
谢谢!
Bryan
布莱恩
1 个解决方案
#1
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
通过SSIS将日期记录导出到excel将始终被视为文本，除非您已经在excel表中有虚拟记录，然后在虚拟行之后将其导入到该excel中。假设您将虚拟行设置为日期格式
#1
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
通过SSIS将日期记录导出到excel将始终被视为文本，除非您已经在excel表中有虚拟记录，然后在虚拟行之后将其导入到该excel中。假设您将虚拟行设置为日期格式