Import Excel data with SQL Server Integration Services

By: Tim Cullen   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Microsoft Excel Integration

Problem

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.

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 When setting up an OLE DB connection for an SSIS package right the Connection Manager, choose New OLE DB Connection, then choose Microsoft Access 12.0 Database Engine OLE DB Provider:

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.

Next Steps

• 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
  • SQL Server Integration Services
  • SQL Server Agent
  • Export data from SQL Server to Excel
  • Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet
  • Microsoft Access and SQL Server Integration
  • SQL Server Reporting with Microsoft Excel

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

View all my tips