Browse by Tags

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

Published 1 September 10 11:50 AM | dougbert

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...

Filed under: known issues, Excel, 64-bit

New Excel components out in beta from Cozyroc

Published 8 June 10 3:41 PM | dougbert

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...

Filed under: Excel, third-party products, custom components, vendors

Download beta of new 64-bit Excel driver

Published 5 April 10 6:43 AM | dougbert

<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...

Filed under: Excel, 64-bit

Importing from Excel: IMEX and mixed data types

Published 21 November 9 3:15 PM | dougbert

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...

Filed under: Excel

64-bit Excel Driver in Microsoft Office 14

Published 28 August 9 3:6 PM | dougbert

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...

Filed under: Excel, 64-bit, connectivity

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

Published 31 March 9 12:35 PM | dougbert

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...

Filed under: Excel

Exporting data from SSIS to Excel 2007: Gotchas

Published 13 February 9 2:49 PM | dougbert

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...

Filed under: known issues, Excel, Import and Export Wizard

Excel, the Office 12 ACE Provider, and performance

Published 20 August 8 10:39 AM | dougbert

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...

Filed under: known issues, Excel, performance

Speaking of writing Office XML...

Published 21 June 8 1:17 PM | dougbert

In my third installment on Excel issues , I proposed writing out Excel XML rather than binary Excel files as one way to avoid both the issues of the Excel driver, and the risks of Excel automation. Recently I came across a DTS custom task that I wrote...

Filed under: DTS, Excel, XML

Excel in Integration Services, Part 3 of 3: Issues and Alternatives

Published 21 June 8 1:5 PM | dougbert

This post is the 3rd and last in a series of 3 planned installments about using Excel data files with Microsoft SQL Server Integration Services. Connections and components Tables and data types Issues and alternatives Introduction Don't expect miracles...

Filed under: known issues, Excel

Excel in Integration Services, Part 2 of 3: Tables and Data Types

Published 18 June 8 11:24 AM | dougbert

This post is the 2nd in a series of 3 planned installments about using Excel data files with Microsoft SQL Server Integration Services. Connections and components Tables and data types Issues and alternatives Tables What's a table in Excel? The source...

Filed under: Excel

Excel in Integration Services, Part 1 of 3: Connections and Components

Published 16 June 8 7:0 AM | dougbert

This post is the 1st in a series of 3 planned installments about using Excel data files with Microsoft SQL Server Integration Services. Connections and components Tables and data types Issues and alternatives The Excel Driver Excel connectivity in SQL...

Filed under: Excel