Introduction

Importing Excel files into SQL Server database is a common task needs to carry out by the DBAs and developers. Also, in case of data warehouse, you need to extract data from various data sources. Most of the times, Excel is one of the data sources.

As you are aware, importing and exporting data from and to the Excel is simple. it is just a matter of drag and drop few data flow controls and configuring them according to your need.

Importing Excel 2007 File

If you are asked to import an Excel file, you can use Excel Source from the Data Flow Sources in SQL Server Integration Services (SSIS) and select correct version from the available list.

You can see that you can only import Excel files up to Microsoft Excel 97-2005 version, which means that you are not allow to import Excel 2007 files from the above control.

However, if you follow below steps, you can import Excel 2007 files into the SQL Server.

1. Drag and drop OLE DB Source data flow source to the data flow task.

2. Double click the OLE DB Source and click New button for OLE DB Connection Manager.

3. Click New button in the Configure OLE DB Connection Manager screen.

4. Select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider from the OLE DB Provider list.

5. Select All option and at the Extended Properties enter Excel 12.0. After this you will see a screen like following image.

You can see the selected provider at the top of the screen.

6. Enter the file name with full path and make sure you have the extension xlsx.

7. After clicking OK button, you will be taken to the initial screen, in which you have to select the worksheet you want.

Exporting to Excel 2007 File

There is no different when exporting to Excel file. it is again you have to modify the destination connection as above.

SQL Server 2008

Though things are difficult in SQL Server 2005, things have become easy with SQL Server 2008. In SQL Server 2008, you simply need to select the Excel 2007 from the drop down.

Like import, you can use same way to export data to Excel 2007 by using Excel destination in SQL Server 2008.