Excel Connection Manager

  • Makale

An Excel connection manager enables a package to connect to an existing Microsoft Excel workbook file. The Excel source and the Excel destination that Microsoft SQL Server 2005 Integration Services (SSIS) include use the Excel connection manager.

Note

On a 64-bit computer, you must run packages that connect to Microsoft Excel data sources in 32-bit mode. The Microsoft Jet OLE DB Provider that connects to Excel data sources is available only in a 32-bit version.

Note

While you can use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2003 or earlier, you cannot use an Excel connection manager to connect to a data source that uses Microsoft Office Excel 2007. Also, you cannot use the Microsoft Jet 4.0 OLE DB Provider to connect to an Excel 2007 data source. To connect to an Excel 2007 data source, use an OLE DB connection manager, and for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB Provider. Then, on the All page of the Connection Manager dialog box, for Extended Properties, enter Excel 12.0. To load data from or to an Excel 2007 data source, use an OLE DB source or an OLE DB destination, respectively. You cannot use an Excel source or Excel destination to connect to an Excel 2007 data source.

When you add an Excel connection manager to a package, Integration Services creates a connection manager that is resolved as an Excel connection at run time, sets the connection manager properties, and adds the connection manager to the Connections collection on the package.

The ConnectionManagerType property of the connection manager is set to EXCEL.

You can configure the Excel connection manager in the following ways:

  • Specify the path of the Excel workbook file.
  • Specify the version of Excel that was used to create the file.
  • Indicate whether the first row of accessed data in the selected worksheets or ranges contains column names.

If the Excel connection manager is used by an Excel source, the column names are included with the extracted data. If it is used by an Excel destination, the column names are included in the written data.

The Excel connection manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources. For more information about the behavior of this provider and driver when used with Excel sources and Excel destinations, see Excel Source and Excel Destination.

Note

You cannot connect to a password-protected Excel file.

Configuring the Excel Connection Manager

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, see Excel Connection Manager Editor.

For information about configuring a connection manager programmatically, see ConnectionManager and Adding Connections Programmatically.

For information about looping through a group of Excel files, see How to: Loop through Excel Files and Tables.

See Also

Tasks

How to: Loop through Excel Files and Tables

Concepts

Excel Source
Excel Destination
Integration Services Considerations on 64-bit Computers

Other Resources

Integration Services Connections
Working with Excel Files with the Script Task

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007
New content:
  • Added a note about how to use a 64-bit computer to run packages that connect to Excel data sources.

12 December 2006
New content:
  • Added a note about the use of Excel 2007 data sources.

5 December 2005
New content:
  • Described the use of column names in data.
  • Provided information about the provider and driver for the Excel connection manager.