Connecting to a Workbook in Excel 2007 Format
To access an Excel 2007 workbook, the OLE DB connection manager 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.
To create an OLE DB connection manager from the Connection Managers area
-
In Business Intelligence Development Studio, open the package.
-
In the Connections Managers area, right-click anywhere in the area, and then select New OLE DB Connection.
-
In the Configure OLE DB Connection Manager dialog box, click New.
-
In the Connection Manager dialog box, for Provider, select Microsoft Office 12.0 Access Database Engine OLE DB.
.gif) Note: |
|---|
|
To connect to a data source that uses Excel 2007, you cannot select Microsoft Jet 4.0 OLE DB Provider for the Data Source.
|
-
On the Connection tab of the Connection Manager dialog box, for Server or file name, type or paste the complete path and file name.
-
On the All tab of the Connection Manager dialog box, for Extended Properties, enter one of the following values based on the file format of the Excel 2007 file:
-
Enter Excel 12.0 Xml for the default file format that uses the .xslx file name extension.
—or—
-
Enter Excel 12.0 for the non-default binary file format that uses the .xslb file name extension.
| Note: |
|---|
|
When you import from Excel 2007, you can specify either Excel 12.0 or Excel 12.0 Xml because the driver infers the correct format from the input file. However, when you export to Excel 2007, you have to specify the Excel file format that corresponds to the file name extension that you have given the output file.
|
To create an OLE DB connection from the SQL Server Import and Export Wizard
-
Start the 32-bit version of the SQL Server Import and Export Wizard.
-
On the Choose a Data Source page, for Data Source, for Data Source, select Microsoft Office 12.0 Access Database Engine OLE DB Provider.
| Note: |
|---|
|
To connect to a data source that uses Excel 2007, you cannot select Microsoft Jet 4.0 OLE DB Provider for the Data Source.
|
-
Click Properties.
-
On the Connection tab of the Data Link Properties dialog box, for Data Source, type or paste the complete path and file name.
-
On the All tab of the Data Link Properties dialog box, for Extended Properties, enter one of the following values based on the file format of the Excel 2007 file:
-
Enter Excel 12.0 Xml for the default file format that uses the .xslx file name extension.
—or—
-
Enter Excel 12.0 for the non-default binary file format that uses the .xslb file name extension.
| Note: |
|---|
|
When you import from Excel 2007, you can specify either Excel 12.0 or Excel 12.0 Xml because the driver infers the correct format from the input file. However, when you export to Excel 2007, you have to specify the Excel file format that corresponds to the file name extension that you have given the output file.
|