Excel driver now supported on server for use by SSIS

A year ago, I wrote a blog post to caution SSIS users that the ACE Provider and its Excel driver were not supported by Microsoft for use on the server. That position has changed - in certain circumstances! - as I learned today from SSIS dev manager David Noor. The download page for the ACE Provider now contains the following new paragraph: The Office System Drivers are only supported under certain scenarios, including: ... 2.To transfer data between supported file formats and a database repository, such as... [More]

Excel driver not supported on the server

This statement about the ACE Provider and its Excel driver caught my attention on an internal discussion list at Microsoft on 12/26/2010:"The current support position from the Office team responsible for the ACE provider (both 32-bit and 64-bit) is that it is unsupported to use the ACE provider on any server-based system. Over here in the XYZ team, we have gone around and around with them and they are very clear. What you are trying to accomplish, regardless of the ‘bit-ness’,  is tech... [More]

Making SSIS work with the 64-bit Excel (ACE) provider

I've previously blogged about the challenges of the new 64-bit ACE Provider that includes the Excel driver, since the 32-bit and 64-bit versions cannot be installed side-by-side. In this detailed post that includes screen shots, Hrvoje Piasevoli from Croatia describes 2 approaches for working with the 64-bit driver from SSIS: Importing data from 64bit Excel in SSIS Since the author's 2 workarounds are (1) the Import and Export Wizard and (2) OPENROWSET, not all users will find them completely satisfying. A ... [More]

New Excel components out in beta from Cozyroc

Yesterday (6/7/2010), Ivan Peev of Cozyroc announced the beta release of version 1.5 of SSIS+, Cozyroc's suite of custom components for SQL Server Integration Services. The new components for Excel in SSIS caught my attention. The announcement summarizes this new release as follows: "The new version includes components for integration with Microsoft Dynamics CRM, enhanced tasks and connections for handling emails, enhanced support of Microsoft Excel workbook files and components for integration wi... [More]

Download beta of new 64-bit Excel driver

<tap><tap> Is this thing on? If you've been curious to test the 64-bit version of the provider and driver for Excel, you can download it from: http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en You specify "Excel 14.0" in the Extended Properties string if, in fact, your are connecting to an Office 14 file. Keep in mind that the 32-bit and 64-bit providers cannot be installed side-by-side. As previously discussed here... [More]

Importing from Excel: IMEX and mixed data types

When we talk about using import mode, "IMEX=1," to resolve a common problem when importing from Excel, we sometimes give or get the impression that setting IMEX=1 automatically imports all data as text. This is an over-simplification. In fact, IMEX=1 causes the value of the Registry setting, ImportMixedTypes, to be applied. ImportMixedTypes commonly has a value of Text. Therefore, if and only if you have a column that contains both numbers and strings, all the values in that column are imported a... [More]

64-bit Excel Driver in Microsoft Office 14

First, the good news... If you use SQL Server Integration Services to load data to or from Excel files or Access databases, then a 64-bit driver is headed your way. This is great news for SSIS users in a 64-bit world. Soon you'll be able to run your packages in 64-bit mode on 64-bit computers, rather than wrestle with obscure settings to force some packages to 32-bit mode. The next version of Microsoft Office, Office 14, will be available in a 64-bit version. This version will include a 64-bit version... [More]

Import from a macro-enabled Excel 2007 workbook (*.xlsm) (CORRECTED)

CORRECTED ON 4/2/09 FROM ORIGINAL POSTING ON 3/31/09 The Office 12 ACE Provider recognizes the .XLSM extension for a macro-enabled Excel 2007 workbook. However, the Excel Connection Manager (in SQL Server 2008 Integration Services) only recognizes the .XLSX file extension for Excel 2007 files. Therefore you have to use the OLE DB Connection Manager (and Source and Destination) with Excel 2007 files that have a file type and file extension other than .XLSX. Importing. You can connect to, and import from... [More]

Exporting data from SSIS to Excel 2007: Gotchas

The default Microsoft Excel 2007 file fomat, with the file extension .xslx, is in fact an XML file, or rather a Zip file that contains multiple XML files. However you can also save an Excel file in binary format, with the file extension .xlsb. For more information on Excel file formats, see File formats that are supported in Excel. Our SQL Server Books Online topic, How to: Connect to an Excel Workbook, contains incomplete and potentially misleading information when it tells you to set Extended Properties ... [More]

Excel, the Office 12 ACE Provider, and performance

Recently an internal Microsoft team ran into performance issues when they switched from using the Jet Provider and writing out Excel 2003 files, to using the Office 12/2007 ACE Provider and writing out Excel 2007 files from their Integration Services package. Their inquiries brought the following information to light: There are 3 reasons for reduced performance with the Office 12 ACE Provider when used with Excel 2007 files, either binary (.xlsb)or XML (*.xlsx): The ACE Provider ... [More]