• Article

How to: Connect to an Excel Workbook

To connect an Integration Services package to a Microsoft Office Excel workbook requires an Excel connection manager.

You can create these connection managers from either the Connection Managers area in SSIS Designer or from the SQL Server Import and Export Wizard.

The data provider that the connection uses depends on the version of the Excel file format:

  • For files that have an Excel 2003 or earlier format, the Excel connection manager, uses the Microsoft Jet OLE DB Provider.

  • For files that have the Excel 2007 or later format, the package requires the OLE DB provider for the Microsoft Office 12.0 Access Database Engine. This provider is installed automatically with the 2007 Microsoft Office system. If the 2007 Office system is not installed on the computer on which Integration Services is running, you have to install the provider separately. To install the OLE DB provider for the Microsoft Office 12.0 Access Database Engine, download and install the components on this Web page, 2007 Office System Driver: Data Connectivity Components. For more information on the file formats that Excel 2007 supports, see File formats that are supported in Excel.

Note

On a 64-bit computer, you must run packages that connect to Microsoft Excel data sources in 32-bit mode. Both the Microsoft Jet OLE DB Provider and the OLE DB provider for the Microsoft Office 12.0 Access Database Engine are only available in 32-bit versions.

To create an Excel connection manager from the Connection Managers area

  1. In Business Intelligence Development Studio, open the package.

  2. In the Connections Managers area, right-click anywhere in the area, and then select New Connection.

  3. In the Add SSIS Connection Manager dialog box, select Excel, and then configure the connection manager.

    For information about the configuration options that are available for this connection manager, see Excel Connection Manager Editor.

To create an Excel connection from the SQL Server Import and Export Wizard

  1. Start the 32-bit version of the SQL Server Import and Export Wizard.

  2. On the Choose a Data Source page, for Data Source, select Microsoft Excel, and then configure the Excel connection.

    For information about the configuration options that are available for this connection type, see Excel Connection Manager Editor.