Importing Excel 2007 File to sqlserver2005 with ssis

最新推荐文章于 2025-12-06 20:45:00 发布

原创最新推荐文章于 2025-12-06 20:45:00 发布 · 773 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#excel #sqlserver #ssis #file #sql server #import

本文介绍如何使用SQL Server Integration Services (SSIS) 导入和导出Excel 2007文件。由于Excel 2007版本的特殊性，通常的数据流控件无法直接支持。文中详细解释了通过配置OLE DB源来实现这一目标的方法，并说明了在SQL Server 2008中这一过程变得更加简单。

SSIS and Excel 2007

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.

Zoom in | Open in new window

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.

Zoom in | Open in new window

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.