Import Excel data with SQL Server Integration Services
By: Tim Cullen | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | More > Microsoft Excel Integration
Our company recently pushed out Office 2007 to all workstations. I'm trying to import an Excel 2007 spreadsheet into SQL Server using the Import/Export Wizard but just can't make it work. I don't even see the driver for Excel. Do you all have any advice? Check out this tip for the solution.
As you've likely noticed some things have changed with the Import/Export Wizard, and even more so with the available drivers. In previous versions of Excel, a driver specific to Excel was available. With Excel 2007 you use the Microsoft Access 12.0 Database Engine OLE DB Provider:
One thing you will notice is that the driver is not available with the 64-bit Import/Export Wizard. To access the driver on a 64-bit system choose "Import and Export Data (32-bit), located in the SQL Server 2008 folder:
Using the Microsoft Access 12.0 Database Engine OLE DB Provider in Import/Export Wizard
To import Excel 2007 spreadsheets into SQL Server perform the following steps:
Start the 32-bit Import and Export Data (32-bit) program, choose the Microsoft Access 12.0 Database Engine OLE DB Provider, and click Properties:
Select the All tab and double-click Data Source:
Enter the file location and name and click OK:
Double-click Extended Properties, enter Excel 12.0, and click OK:
Click OK twice and proceed through the wizard. Once on the screen used to map the source and destination you have the option to Preview the data:
Using the Microsoft Access 12.0 Database Engine OLE DB Provider in SSIS
Enter Excel 12.0 in the Extended Properties section and the file location and name in the Data Source section:
Once complete you can preview the data by adding a Data Flow Task, adding the OLE DB Connection to the OLE DB source.
There are a few things to consider when using this driver. First, the driver will work for both Excel 2003 and 2007 spreadsheets so if you are setting up an SSIS package to import data you don't need to switch drivers for each file type. Second, if you plan on having the SQL Server Agent execute SSIS package then you will likely need to install the Office 2007 System Driver: Data Connectivity components.
- Work with the Import/Export Wizard to get a feel for how to configure the driver for import of data housed in an Excel 2007 spreadsheet
- Download the 2007 Office System Driver: Data Connectivity Components
- Check out these related tips
About the author
View all my tips